Referencing the KPI value from the previous day


#1

Hi everyone,

i am interested in an OLAP table, which counts lets say the number of apples in a basket. I have the number of apples put into the basket and the number of apples taken from it day by day. So, the formula would be:
Apples_in_the_basket(t)=apples_in_the_basket(t-1)+new_apples(t)-apples_taken(t).
Can it be implemented in the OLAP table?
Another Question is…for the very first row Apples_in_the_basket(t=0) I would need the initial number of apples, i.e. the number to start with. I would like to use a fixed number for that. Is it doable?

Best regards,
Maria


#3

Hello Maria,

I understand what you are trying to explain but I am not sure about the format in which this data is available to you. Do you have a csv file or db table in Celonis that has this change information in it?

Best,
Henry


#4

The Change Info is in the EventLog in CELONIS.


#5

Hello Maria,

so I assume you have this as some activity (Change quantity) and in the same row there is the “amount changed by” information (or similar). In that case you can calculate a KPI like

SUM(CASE WHEN ACTIVITY_COLUMN = ‘Change Quantity’ THEN “Amount Changed by” ELSE 0 END)

You can display this as a KPI in an OLAP table. The starting number can just be added to this formula. Just be mindful that there can be data type issues if e.g. the Amount changed by column contains decimals and you try to add an integer value.

Best,
Henry