Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Compare complete months only


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

T

4 Replies
s_kunte23
Contributor III

Re: Compare complete months only

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

Not applicable

Re: Compare complete months only

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


s_kunte23
Contributor III

Re: Compare complete months only

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

s_kunte23
Contributor III

Re: Compare complete months only

Hi,

please close this thread if this helped you!

Best regards

Stefan

Community Browser