Hi all,

I have a set of IDs/Case Keys that is spread accross 2023-2025. Focusing on 2024, I would like to calculate the daily average based on the Last activity dates.

These IDs are divided in products, so when I create an OLAP table in Studio Views, then it takes the IDs per product, for large samples it’s closer to a correct daily average. On the other hand, when the product has 1 or 2 case IDs, then the Daily average is far from correct.

Instinctively, I believe I should create a DOMAIN_TABLE but it doesn’t work. I tried using GLOBAL but it is not accepted.

Hereby the code i’m using:

Originaly:

KPI("number_of_cases")

/

(  MAX (REMAP_TIMESTAMPS (PU_LAST("PROD_CASE_TABLE_DR", "PROD_ACTIVITY_TABLE_DR"."EVENTTIME"), DAYS, WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) ))

-

MIN (REMAP_TIMESTAMPS (PU_LAST("PROD_CASE_TABLE_DR", "PROD_ACTIVITY_TABLE_DR"."EVENTTIME"), DAYS, WEEKDAY_CALENDAR ( MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY ) ))

+1)

or

KPI("number_of_cases")

/

(DATEDIFF ( dd, PU_LAST(DOMAIN_TABLE("PROD_CASE_TABLE_DR"."REQUEST TYPE"), "PROD_ACTIVITY_TABLE_DR"."EVENTTIME")

,PU_LAST(DOMAIN_TABLE("PROD_CASE_TABLE_DR"."REQUEST TYPE"), "PROD_ACTIVITY_TABLE_DR"."EVENTTIME")))

The formula works per row in the OLAP table, I need to make this temporary filter work to obtain the days for the selected months (if I select 3 months, 66 working days, if I select 1 month, 22 working days, and so on) .

Thank you for your help!