topic Re: Help needed with max YEAR MONTH -1 in QlikView

Help needed with max YEAR MONTH -1

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help needed with max YEAR MONTH -1

sunny_talwar — Thu, 12 Jan 2017 14:08:40 GMT

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), '###,###.')

Re: Help needed with max YEAR MONTH -1

Anonymous — Thu, 12 Jan 2017 14:18:55 GMT

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

Re: Help needed with max YEAR MONTH -1

sunny_talwar — Thu, 12 Jan 2017 14:23:21 GMT

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?

Re: Help needed with max YEAR MONTH -1

swuehl — Thu, 12 Jan 2017 14:25:45 GMT

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), '###,###.')

Re: Help needed with max YEAR MONTH -1

Anonymous — Thu, 12 Jan 2017 15:06:33 GMT

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!