How to get a count of only max month and quarter

[pranaview]

Hi Guys,

Requirement: I need to compare KPIs of current and previous month/quarter with user selections taken into consideration. For ex: if there is no selection made by the user then by default i need to compare the result of max month available in the data set and the previous but if user wants to go back and select some month in the past for ex Mar-20 then i need to compare Mar-20 with Feb-20 and show the difference. Similarly if the user selects Q1 for year 20 then i need to compare the results for Q1-2020 with Q4-2019. I hope it has made sense so far.

Now, i am trying to do it within the set expression using a variable which is basically an expression to get max month but i am not getting the right output. If anyone has worked on similar requirement then any suggestion would be really helpful.

Thanks,

Pranav

[Taoufiq_Zarra]

can you share a sample data and the expected output from this sample ?

[vamsee · Accepted solution]

May be

=If( GetSelectedCount([Year]) =1,
(Sum({<Year={"=$(=Max(Year))"}>}Amount)
-
Sum({<Year={"=$(=Max(Year)-1)"}>}Amount)
),

If( GetSelectedCount([Quarter]) =1,
(Sum({<Quarternum={"=$(=Max(Quarternum))"}>}Amount)
-
Sum({<Quarternum={"=$(=Max(Quarternum)-1)"}>}Amount)
),
If( GetSelectedCount([Month]) =1,
(Sum({<Month={"=$(=Max(Month))"}>}Amount)
-
Sum({<Month={"=$(=Max(Month)-1)"}>}Amount)
),
If( GetSelectedCount([Week]) =1,
(Sum({<Week={"=$(=Max(Week))"}>}Amount)
-
Sum({<Week={"=$(=Max(Week)-1)"}>}Amount)
),
(Sum({<Month={"=$(=Max(Month))"}>}Amount)
-
Sum({<Month={"=$(=Max(Month)-1)"}>}Amount)
)
))))

Note: Create field Quarternum, basically assigning a number for every quarter in each year, quarters are repeated in each year. 

[pranaview]

Hi,

Thanks for the response!

I had also applied similar approach using the GetSelectedCount() function, only thing is that i had to add '1' just before the set modifier starts otherwise it won't work if user selects older months/quarters etc.

Regards,

Pranav 

[pranaview]

Hi,

I have ran into a problem now though. If i don't include 1 as my set identifier then the Previous Month calculation doesn't work when i select any single month because there's no previous month with the that selection so i had to include 1 before the modifier but now when i do that it doesn't respect any filter selection that is not present in the set expression which is really important as a use case. 

Any suggestion on this please?

Thanks!

[pranaview]

 Okay I got it!