Skip to main content

Hi everyone, I need help with a filter.

I want to put a filter on a kpi that only allows it to show the previous months data. The filter must be dynamic so that when I select a month from the date picker that it would always calulate the previous months data.

I am currently using this PQL:
 

FILTER "CEL_ACTIVITIES_OTT"."first_eventtime" >=ADD_MONTHS(PU_MAX ( CONSTANT ( ), FILTER_TO_NULL(ROUND_MONTH("CEL_ACTIVITIES_OTT"."first_eventtime"))),-1)

AND

"CEL_ACTIVITIES_OTT"."first_eventtime"<PU_MAX ( CONSTANT ( ),FILTER_TO_NULL( ROUND_MONTH("CEL_ACTIVITIES_OTT"."first_eventtime")))

The problem is that I have to select both months from the date picker and Ideally I would like to only select one month. 

Let me know if I am missing anything, but if your current issue is that you have to select two dates from the date picker (and if your PQL for this already works after you have filtered to a date) than maybe a solution could be to switch the way you pick the date to a dropdown that selects the month - you could store the months in a KPI (where you round the month of the timestamp) and map that dropdown to select from that kpis values?


I have already created a dropdown that allows you to select a month. But the user still needs to select two consecutive months to be able to see the previous months data.


Oh I think I understand - so you are currently filtering the view to two months and then taking data from the earlier of the two? Maybe instead a solution could be to have that dropdown set a variable with the month instead of filtering the view to two months? That way you could set that variable to a month, do some sort of lag to get the previous month, and use that previous month for calculations? Let me know if you need me to explain further!


Reply