How to get Active Users count from rolling 12 months prior to user selected month
Hi All,
I am having a requirement like to find Active producers from attached image. Here user may select any Date range. This example is if selected Jan-2021 ,
scenario 1) Jan- 2021 - Will be the Maximum date and here it should take all the values which are not null. For this used : count({<MonthYear={"$(=max(MonthYear))"}>}distinct Producer)
scenario 2) We need to take rolling 12 months from before the user selection date i.e if user selected Jan-2021, Then we should take the data from Jan-2020 to Dec-2020 data and we should take a count of Numbers which are greater than 12 and also only if Jan 2021 is having null value
Example from the image:
Producer A - Will be taken into count, as Rolling 12 months(Jan2020 - Dec2020) having count >12 and also Jan-2021 is null Producer B - Should not be considered as it is having value in Jan-2021 Producer C - also same, it is having value in Jan-2021 Producer D- Should not considered since we don't have any values for all 13 months Producer E - Even though Jan-2021 is null , but still the rolling 12 months count is not greater than 12 , so should not be considered
So here from scenario 1 : B, C and G are considered i.e 3 Producers from scenario 2 : A and J are considered i.e 2 Producers.
So if user selects Jan-2021 , count of 5 producers should show in KPI
