How to find the previous month values and compare with current month

[Navars]

Hi All,

I have customers and their subscriptions both are coming from two different tables.

I have to find the count of customers who have the more subscriptions compared to last month .

eg: cust1 have 2 subscriptions in last month but in current month the same customers has 3 subscriptions so we need to count this cust id .

cust2 have 3 subscriptions in last month but in current month the same customers has 2 subscriptions so we dont consider this cust id 

passing Dim as Valid from month , i am showing last 12 months data in the bar chart

How to achieve this with set analysis.

Thanks in advance

Regards,

Sra1..

[JordyWegman]

Hi Navars,

I would say something like this:

Aggr(
    IF( Count( {$< _indLastMonth = {1} >} Subscriptions ), Customer) < Count( {$< _indCurrentMonth = {1} >} Subscriptions )
, Customer),
1,
0
)

You will need to have an indicator field that tells you if the month is last or current. Let the field be a 1 or 0 (1 of correct, 0 is false). Do you know how to create this?

Then aggregate this by customer and compare this.

Jordy

Climber

[Navars]

Hi Jordy,

Thanks for the reply.

But here i need to show 12 month data in bar chart, not current month and previous month comparison.

can you please share the logic for creating flags for current month and previous month.  i will try the above logic 

Regards,

Sra1..

[JordyWegman]

Hi,

The logic would be IF( Year( [YourDate] ) & '-' & Month( [YourDate] ) = Year( Today() ) & '-' & Month( Today() ) = 1, 0 ) for the current year month. And for the previous month you will need to add AddMonths( Today() , -1) instead of Today().

But since you want to for all the months, maybe you can just create a separate table for this in your data model? Or do you want it to react on user selections?

Jordy

Climber