I’ve got trouble finding a solution to calculate how many cases have a specific status at the end of each day. There are only three possible values the status can have (A,B,C). To calculate how many cases have the status ‘A’, I am using the following formula:
RUNNING_TOTAL(COUNT(DISTINCT CASE WHEN “MAIN_TABLE”.“STATUS” = ‘B’ THEN “MAIN_TABLE”.“CASE_ID” ELSE NULL END))
RUNNING_TOTAL(COUNT(DISTINCT CASE WHEN “MAIN_TABLE”.“STATUS” = ‘C’ THEN “MAIN_TABLE”.“CASE_ID” ELSE NULL END))
The other two KPIs are calculated the same way, only the Status fields are changed accordingly.
What I am now looking for is a time range as the dimension. This range should include every single day since one year. The Creation Date of the Case or the Eventtime of an Activity won’t work, because the statuses can be changed on a daily basis but does not have to be. If none of the statuses are changed, then this day should still appear in my OLAP table.
Do you maybe have a solution @d.becher?