Question
Floating 90 day date range for a KPI card
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.
+2Hi,
I think youre 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
DavidThanks 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. 
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
)
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
)+2Hey,
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
DavidEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.