Rolling values in a chart


#1

Hello!

Is it possible to create a Chart with a rolling values of a variable? for example, “open cases” for the last 30 days on a daily Basis? Is it also possible to track rolling averages?

Thank you!


#5

Hello Maria,

you could create a component filter that references the last creation date and filter on this date minus 30 days. Further, you can take a look at the moving functions in the PQL-reference.

Best,
Henry


#6

Hello,

could you please be more precise and give an example of this filter + PQL Moving functions?
I am not sure that the “last creation date” would work for an OLAP-Table with months as Dimension.

Thank you


#7

Hello Maria,

a simple example of a filter option with this rolling window would be:

FILTER DAYS_BETWEEN(VBAP.ERDAT, add_days(TODAY(), - 30)) < 0

In the O2C process.Regarding your comment on the months as dimension, I do not quite understand what you mean. Generally this should work in combination with date functions as dimension.

Best,
Henry


#8

Hello Henry,

in my OLAP-Table i would like to have months or days on the x-axis. Hense, the rolling should calculate the values of my KPI for interval of the last month/30 days. i.e. for the 1.1.2017 it is a an average value for the time interval 2.12.2016-01.01.2017, for the 2.01.2017 it is the average value for the time interval 3.12.2016-2.1.2017 and so on. So the interval for the calculation always moves by 1 day. Can I still use the TODAY function.
Can u also explain what VBAP.ERDAT does, i.e. what kind of column is that?

Thank you.

Best,
Maria


#9

Hello Maria,

This is what the moving average function can be used for. Simply specify the relevant dimension and set the KPI to something like

MOVING_AVG(COLUMN.KPI, -15, +15)

The VBAP.ERDAT is a creation date, but was used as an example. I just wanted to highlight that a date column is used here.

Best,
Henry


#10

Oo…I dont have the MOVING_AVG function in my library


#11

stehe bestimmt auf dem Schlauch:(


#12

Hello Maria,

this is a relatively new feature. It might be possible that it is not available in the version you are curently using. In this case an update of your Celonis version might be necessary to use this feature. The current version is 4.4, if you do have this version please contact servicedesk@celonis.com with screenshots of this function (or the lack of it) in the PQL reference in the custom KPI window. Should you use an earlier version and you think the update is worthwhile for your use case, please also contact servicedesk@celonis.com so we can arrange an update.

Best,
Henry


#13

thank you very much! that definitely explains why i don’t have the moving average function…we still have 4.2. Version.

Best regards,
Maria


#14

very interesting to know! thanks for sharking!