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
