topic Re: Compare complete months only in QlikView

Compare complete months only

Tue, 14 Oct 2014 10:41:41 GMT

Hi all,

We load sales results monthly, and sometimes these come in the form of groups of weekly files.

This is leaving me with an issue for my month on month growth figures.

Ie - today it is comparing October 1-3 against September 1-3,

Whereas what I want it to show is September growth over August as these are the last 2 complete months I have.

Currently I'm using :

=Num(Sum({<Month={$(=vMaxMonth)},Year={$(=vMaxYear)}>}Revenue),'$#,##0')

to give me what should be last month.

---Variable for Max Month is a pretty standard : =Month(Max(invoice_date))

And

Num(Sum({<Month={$(=vPriorMonth)},Year={$(=vMaxYear)}>}Revenue),'$#,##0')
For the prior month., where ---- month(addmonths(max(invoice_date) ,- 1)) is my vPriorMonth

Does anyone know how I can ignore those months that are showing dates less than ...let's say the 26th?

Thanks in advance

Re: Compare complete months only

Anonymous — Tue, 14 Oct 2014 10:58:14 GMT

Hi,

you can try:

month(addmonths(if( match(max(invoice_date) , monthend(max(invoice_date))), max(invoice_date), addmonths(max(invoice_date),-1)),-1))

Hope this helps.

Best regards

Stefan

Re: Compare complete months only

Tue, 14 Oct 2014 11:07:41 GMT

Hi Stefan,

Thanks, This is throwing out August for me.

I did modify it to

month(addmonths(if( match(max(invoice_date) , monthend(max(invoice_date))), max(invoice_date), addmonths(max(invoice_date),-1)),0))

And then it gives me September.

The problem persists when a month is actually chosen though.

If - for example, I chose April (which is complete) then these expressions give me February and March respectively, which is not what I'm after.

Thanks though

Re: Compare complete months only

Anonymous — Tue, 14 Oct 2014 11:18:38 GMT

Hi,

I guess it's due to your invoice_date. how is your calendar programmed ? Full date range for every month? Then replace invoice_date with the key date in the calendar.

edit: this is the formula for your prev Month Variable. For your max Month Variable use:

month(if( match(max(invoice_date) , monthend(max(invoice_date))), max(invoice_date), addmonths(max(invoice_date),-1)))

Change the date to your calendar date field.

If not it becomes complicated because you need to replace monthend with lastworkdate(start_date, no_of_workdays {, holiday}), but as you can see second parameter will require addtional work.

Hope this helps

Best Regards

Stefan

Re: Compare complete months only

Anonymous — Thu, 16 Oct 2014 18:28:33 GMT

Hi,

please close this thread if this helped you!

Best regards

Stefan