topic Compare current month with next month of last year in QlikView

Compare current month with next month of last year

Thu, 30 Oct 2014 16:43:49 GMT

Hi guys,

There is something I just cannot get to work the way I want it to. The issue is the following:

I want to show a pivot (or straight) table with sales information of the current year and month and I want to compare this with the sales of last year's next month. So basically in three words: compare the current month with the following month of last year.

=> October 2014 vs. November 2013

=> March 2013 vs. April 2012

Could you guys help me please? The expression to start with is, eg. sum({<date={"March"}>}Sales)

or sum(Sales) if March will have been selected in a list box.

Thanks in advance!

Re: Compare current month with next month of last year

Thu, 30 Oct 2014 17:37:48 GMT

Hi,

Sum(Sales) for selected Month

For following month last year

Sum({<Year={'$(=$(year(Date)-1))'},Month={'$(=$(Month(Date)+1))'}>}Sales)

Not sure how is the date format in your application.

Change the expression according to the date format.

Re: Compare current month with next month of last year

Fri, 31 Oct 2014 16:11:47 GMT

Hi Shruti,

Thanks for your reply. Since I am 'New to Qlikview', I do have some questions.. It is not completely clear to me what exactly I should put for the "Date". You are using the words Year, Month and Date.

I have two dimensions, Month (jan, feb, mar, apr, ...), Year (2012, 2013, 2014) and part of the expression "Sales".

What should I use for "date"?

Thanks in advance!

Re: Compare current month with next month of last year

Tue, 04 Nov 2014 10:32:14 GMT

Hi all,

actually, this solution will not work when for example you select december of a certain year.

"Month={'$(=$(Month(Date)+1))" will return 13 as month, which doesn't exist.

The correct way to handle this is to use the funtion "addmonths".

The solution for the question of zzzzipke would be:

sum({$<Year={$(=year(addmonths(makedate(only(Year),only(Month),'1'),'-11')))},Month={$(=month(addmonths(makedate(only(Year),only(Month),'1'),'-11')))}>}Sales

)

Regards,

Yves