Filter based on date and certain activity

Dear Celonis Community,

I am having trouble creating a bar chart which tracks the amount of unsend orders. I need to filter all cases which do not have the “Delivered” activity and at the same time, have a preferred delivery date < current date. I managed to write two seperate statements but get errors trying to combine both. Maybe somone can help.

SUM(MATCH_ACTIVITIES(EXCLUDING_ALL[‘Delivered’]))

and

SUM(CASE WHEN TO_TIMESTAMP(“CASES”.“Preferred_Delivery_Date”, ‘’) < HOUR_NOW(‘PST’) THEN 1.0 ELSE 0.0 END)

Kind regards.

Konstantin

Hi Konstantin,

how did you you try to combine the two statements? You can simply connect both conditions with an AND inside a CASE WHEN:

SUM ( 
CASE WHEN MATCH_ACTIVITIES(EXCLUDING_ALL['Delivered']) = 1 
AND TO_TIMESTAMP("CASES"."Preferred_Delivery_Date", 'your pattern here') < HOUR_NOW('PST') 
THEN 1 ELSE 0 END 
)

Best
David