topic Re: Month-on-Month comparison in QlikView

Month-on-Month comparison

Mon, 02 Mar 2015 07:42:04 GMT

Hi,

I would like to calculate the Month-on-Month variant in the pivot table I created.

How should I go ahead doing this?

Re: Month-on-Month comparison

jonathandienst — Mon, 02 Mar 2015 08:27:29 GMT

Ideally, you will have a month sequence number in your calendar (lets call it MonthSeq), defined like:

Year * 12 + Month (where Year and Month are the respective fields from your calendar)

Then month on month would look something like:

Sum({<MonthSeq={"$(=Max(MonthSeq))"}>} Amount) - Sum({<MonthSeq={"$(=Max(MonthSeq)-1)"}>} Amount)

HTH

Jonathan

Re: Month-on-Month comparison

jonathandienst — Mon, 02 Mar 2015 08:29:14 GMT

If you are making selections on Month or Year, then the set expressions should be:

{<MonthSeq={"$(=Max(MonthSeq))"}, Year, Month>}

{<MonthSeq={"$(=Max(MonthSeq)-1)"}, Year, Month>}

to override these selections and only use the MonthSeq filter.

Re: Month-on-Month comparison

Mon, 02 Mar 2015 09:36:19 GMT

Im trying to attach my qvw, but it is 300mb. how do I attach a smaller qvw in order to show what im trying to achieve?

Re: Month-on-Month comparison

kamalqlik — Mon, 02 Mar 2015 09:46:11 GMT

Hi Gerald ,

Please make restriction on particular field ....

1) Either take data for two month only

2) For particular Procuct Code etc

in where Clause and then upload your QVW. Than only I can look after your problem.

Regards

Kamal

Re: Month-on-Month comparison

Mon, 02 Mar 2015 09:52:51 GMT

Hi,

attached is my qvw. what I would like to do is firstly show the M-O-M change, and then the Y-O-Y change next to that, as a percentage,

Re: Month-on-Month comparison

kamalqlik — Mon, 02 Mar 2015 09:55:17 GMT

please upload it again ....As it is showing error while downloading

Re: Month-on-Month comparison

Mon, 02 Mar 2015 09:58:08 GMT

Here you go, uploaded again.

Re: Month-on-Month comparison

kamalqlik — Mon, 02 Mar 2015 10:06:03 GMT

Hi Geral Month on Month Comparison ....

I understand like this for example we are in March-2015 than you want the comparison for this month and previous month...

and so on...

Please clarify your requirement little bit more

Regards

Kamal

Re: Month-on-Month comparison

jpenuliar — Mon, 02 Mar 2015 10:07:26 GMT

Hi Gerald,

here's a good example (with other tips) by Lee Mathews

Qlikview how to:

http://community.qlik.com/docs/DOC-5486

Re: Month-on-Month comparison

Mon, 02 Mar 2015 10:08:14 GMT

Yes,

and also the change in Feb 2014 to Feb2015 as the Y-o-Y change.

Re: Month-on-Month comparison

kamalqlik — Mon, 02 Mar 2015 10:38:14 GMT

Hi Gerald,

Use this function

Floor(Monthstart(Date )) as MonthYear and upload the QVW again.

I will provide you the solution.

Regards

Kamal

Re: Month-on-Month comparison

kamalqlik — Mon, 02 Mar 2015 11:23:12 GMT

Hi Gerald,

I was waiting for your reply...

I am mentioning the point that you have to do in your QVW.

1) Make a filed MonthYear at backend as

Floor(Monthstart([DATE REPORTED])) as [MonthYear]

2)Reload the Application

3) Make an variable for this year MaxDate ie Feb2015

vMaxMonth= Floor(num(Max( [MonthYear]))) for Feb 2015

vMaxMonthLastYear =Floor(num(AddMonths( Max([Month Year]),-12)))

Than add two expression one for this year and one for last Year

Expression 1) Count({<[MonthYear]={$(vMaxMonth)}>}[Claim No]).... This will give data for feb2015

Expression 2) Count({<[MonthYear]={$(vMaxMonthLastYear)}>}[Claim No])..... This will give data for feb 2014.

I hope it will help you.

Regards

Kamal

Re: Month-on-Month comparison

Mon, 02 Mar 2015 11:27:44 GMT

Here is the reloaded attachment with changes made.

Re: Month-on-Month comparison

Mon, 02 Mar 2015 12:05:26 GMT

Attached I have added the results that I am trying to show, but done in excel.

see last two columns, highlighted in yellow.