Get the latest month of consumer based on the filter selection.

[onkarkulkarni]

Hi Team, I have a requirement to display the breakdown of the Consumers by category in the Consumer's latest reporting month. I have fields in the data model - ConsumerID, ReportingMonth,Category and Product, and product Type.

User want to first select values from the product and based on that the Status of the latest month should be considered. So I can't calculate the Latest reporting month per consumer in the script. It has to be dynamically calculated based in the filter selection. so it has to be a set analysis expression to compute the latest month.

For example, The maximum Month for the Consumer -1 is March2023 , so without any filter selection it will be considered under category - 'No' but if user selects product Type - Vehicle , then the maximum month becomes Feb2023 and the consumer should be counted under 'Yes' category.

Hope I am able to explain the requirement. Thanks in advance.

Consumer	Month	Category	Product	Prodcut Type
1	Jan-23	Yes	Car	Vehicle
1	Feb-23	Yes	Bike	Vehicle
1	Feb-23	No	Camera	Gadget
1	Mar-23	No	Mobile	Gadget
1	Apr-23	No	Laptop	Gadget
2	Jan-23	Yes	Mobile	Gadget
2	Feb-23	Yes	Bike	Vehicle
2	Mar-23	No	Mobile	Gadget
2	Apr-23	Yes	Car	Vehicle
3	Jan-23	Yes	Car	Vehicle
3	Feb-23	Yes	Car	Vehicle

[tresesco]

I couldn't match your explanation with example. However, it looks you need firstsortedvalue(). Try that; if can't get it to work. Try to share a output with no selection and one with a specific selection.

[onkarkulkarni]

Hi Tresesco, thanks for your reply. let me add a simple example for the same.

[onkarkulkarni]

Hi @tresesco  , I have attached the sample data file and the expected result. 

Basically, I want to show the Consumer count across the two categories - Yes and No. the latest category should be considered for each consumer,after applying the filter - ProductType. I have attached the expected results without any filter, Vehicle filter applied and Gadget filter applied. 

Please let me know if there is any confusion. Thanks!

[tresesco · Accepted solution]

Count( DISTINCT if(ReportingMonth= Aggr(NODISTINCT Max( ReportingMonth),ConsumerID) , ConsumerID))

[onkarkulkarni]

@tresesco  - Thanks a lot, I will just try to merge this into the actual app to see if it's working as expected. But I believe it will.