Ive 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_TABLE(MAIN_TABLE)) -

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 wont 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?

Best Regards,

Benedict Lang