Question
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
+2Hi 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
DavidEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.