Get number of Active Users for selected months.

[Dhamodharan_Meiyalagan]

Hi there, I’m stuck up with one task below. I need to find the active users based on the month filter selection in the report. Consider the below sample data set. Aug-21 Sep-21 Oct-21 Customer A Customer A Customer A Customer B Customer C Customer C Customer D Customer D Customer E When I'm filtering for the last 3 months , I would like to to see the count as 1 customer because Customer A is active for all the months. what I get here is the total count as 9. I'm using count operation. I'm not getting the calculation of how to implement this logic. Can somebody help? Thanks, Dhamodharan.

[BenjaminT · Accepted solution]

Hi,

Is the condition that in order to count, the customer must have been active for ALL of the currently selected months? If so, you could try the following expression:

count(if(aggr(count(distinct month), customer) = '$(=count(distinct month))', customer))

where month and customer are your fields in the app.

Thanks

Ben

[Dhamodharan_Meiyalagan]

Hi Ben,

Thanks for your reply. It provides the correct count of users now.

If I want to include a Product dimension inside this logic, for example, I want to see the number of active user for the continuous months broken down by product? 

I appreciate your time. 

Thanks,

Dhamodharan.

[BenjaminT]

Hi Dhamodharan,

You should be able to use Product as a dimension in a chart (table or bar chart most likely) and then use the expression above as the measure to see the breakdown

Does that work?

Ben

[Dhamodharan_Meiyalagan]

Hi Ben,

The below code works for adding product dimension.

=count(if(aggr(count(distinct month), customer,product) = '$(=count(distinct month))', customer))

Thanks for your time. Much appreciated.

Thanks,

Dhamodharan.