Help needed with max YEAR MONTH -1

[Anonymous]

Hi there,

I want to show in a straight table the total count of emails and then in another column the total count of emails -1 month.

This way I can show the growth vs the previous month. 

Right now to achieve the max total I use:

=num(count( distinct(email)), '###,###.')

I have a calendar attached to my data too so I want something like the expression above -1 YEAR_MONTH.

Let me know if you need any further info to help me.

I am trying to get this simple formula done today.

Many thanks in advance,

Harry

[sunny_talwar]

May be look here:

Dates in Set Analysis

Without knowing how you have created YEAR_MONTH in the script, I am making an assumption that it is created using something like this:

LOAD Date(MonthStart(TempDate), 'YYYY-MMM') as YEAR_Month

if this is true, then you can try this:

=Num(Count({<YEAR_MONTH = {"$(=Date(AddMonths(Max(YEAR_MONTH), -1), 'YYYY-MMM'))"}>}DISTINCT email), '###,###.')

[Anonymous]

Thanks for the reply.

The YEAR_MONTH was created with MMM_YYYY.  I know it should be called Month Year.  It's just the master calendar I am using.

I did change your formula to that code and got the totals for the previous month.  Not the overall total up until that month.

If my total now is 100,000 in Jan, I want to show that in Dec it was 90,000

Therefore it went up 10,000 this month.  Once Feb hits it should show a total of like 105,000 then I can use the -1 to compare to the 100,000.

Can you assist in changing to that?

Thanks

[sunny_talwar]

So you are looking for cumulative value? Also, where exactly are you looking to view this information? Straight table or pivot table or text box object?

[swuehl · Accepted solution]

Maybe just add a numeric search:

=Num(Count({<YEAR_MONTH = {"<=$(=Date(AddMonths(Max(YEAR_MONTH), -1), 'YYYY-MMM'))"}>}DISTINCT email), '###,###.')

or maybe just

=Num(Count({<YEAR_MONTH = {"<=$(=Num(AddMonths(Max(YEAR_MONTH), -1)))"}>}DISTINCT email), '###,###.')

[Anonymous]

Thanks a lot everyone.

=Num(Count({<YEAR_MONTH = {"<=$(=Num(AddMonths(Max(YEAR_MONTH), -1)))"}>}DISTINCT email), '###,###.')

Worked perfectly for me in my straight table.

Cheers!