Unattached time dimension

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

Best Regards,

Benedict Lang

1 Like

Hey Bene,

from what I know we cannot do this yet, but we are planning to improve the RANGE functionality soon such that it should be possible to solve this question then.
Workaround would be to create a table with a date column containing all dates on database side and then join it to your table over the date column.

Maybe somebody else has a better solution, though.

Best
David