How to do a Running Last based on table

Hello all, I spent quite some time exploring all possible functions but I’m unable to solve this.
I need to do a running last based on the table as below. I will then use this to minus off the actual quantity. I’ve explored the MOVING_X function, PU_X function and it doesn’t seem to give me what I need. Any ideas? Appreciate the inputs!

Hello impedance,

Thanks for reaching out! What you are looking for is a combination of LAG() and COALESCE().

Lag() returns the preceding row with a certain offset (in your case 1) based on a defined ordering and partitioning. NULL values are skipped.

Additionally, you need COALESCE() which returns the first argument that is not null. The reason is that LAG() will result in an empty row for each first occurrence of a value in TGT_QUANTITY.

For further details an examples, please refer to the Celonis Cloud Help.

Assuming a sorting by EVENTTIME and a partitioning by Case Key, a solution to your case may look like this:

COALESCE(
"_CEL_XXX_ACTIVITIES"."TGT_QUANTITY",
LAG ( "_CEL_XXX_ACTIVITIES"." TGT_QUANTITY ",
ORDER BY ("_CEL_XXX_ACTIVITIES"."_CASE_KEY", "_CEL_XXX_ACTIVITIES"."EVENTTIME"),
PARTITION BY ("_CEL_XXX_ACTIVITIES"."_CASE_KEY")))

I hope this formula and the explanation help solving your problem.

Edit Note: You could also possibly use the ACTIVITY_LAG function, with which you wouldn’t need to use the PARTITION BY and ORDER BY as it already orders by case.

Viana