Floating 90 day date range for a KPI card

#1

In a KPI card, I would like to display only the activities that occurred for that KPI for the last 90 days. I know the below is incorrect but logically it is as close as I can get without help.

FILTER “_CEL_P2P_ACTIVITIES”.“EVENTTIME” BETWEEN TODAY() AND (TODAY() - 90)

The KPI card works so I am looking for the code to “tack onto” to get the 90 day window.

Please advise! Thanks.

#2

Hi,

I think you’re already quite close. You need to use ADD_DAYS to add/subtract days from a timestamp. So I would suggest the following formula:

FILTER "_CEL_P2P_ACTIVITIES"."EVENTTIME" BETWEEN ADD_DAYS(TODAY(), -90) AND TODAY();

Cheers
David

#3

Thanks for the quick response David!

Please bear with me here…
Using the Automated Activities formula out-of-the-box, where is the appropriate spot to insert the suggested FILTER statement? I assumed that I am filtering first before the SUM function but that did not work. :frowning:

SUM(
CASE WHEN “CEL_P2P_ACTIVITIES"."ACTIVITY<%= language %>” IN (<%= activities_used_in_automation_rate %>)
AND ("_CEL_P2P_ACTIVITIES".“USER_TYPE” IN (<%=auto_user_type%>) OR ISNULL("_CEL_P2P_ACTIVITIES".“USER_TYPE”) = 1)
THEN
1.0
ELSE
0.0
END
)

#4

Hey,

the FILTER is always applied before executing the aggregation, so it should work. What does “not work” actually mean? Do you get an error or unexpected results? Do you have any other dimensions in the component?

But you can also include the condition inside your CASE WHEN instead of the component filter:

SUM(
CASE WHEN "CEL_P2P_ACTIVITIES"."EVENTTIME" BETWEEN ADD_DAYS(TODAY(), -90) AND TODAY() THEN NULL
WHEN "CEL_P2P_ACTIVITIES"."ACTIVITY" <%= language %>” IN (<%= activities_used_in_automation_rate %>)
AND ("_CEL_P2P_ACTIVITIES"."USER_TYPE” IN (<%=auto_user_type%>) OR ISNULL("_CEL_P2P_ACTIVITIES"."USER_TYPE") = 1)
THEN
1.0
ELSE
0.0
END
)

Cheers
David