topic Re: Month on Month? in QlikView

Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 01:30:22 GMT

Hi,

I need to generate a table that consist of two years data, YOY comparison and MOM comparison.

My dimension is Month so it will tabulate monthly data for two years

Basicially I solved my YOY by using column 1 and 2 (2011 and 2012)

However, I am stuck in MOM as I have no idea to ask Qlikview to use Month minus Month-1 data

Any gurus can help on this?

is there such a thing of Column(2) - Column(1)-1?

Re: Month on Month?

Mon, 10 Jun 2013 02:28:10 GMT

Hi,

Do you mean month vs last month comparison?

Regards,

Janzen

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 02:31:30 GMT

Yes,

Therefore my MOM , ie March value will be March minus Feb, April value will be April minus March

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 02:40:20 GMT

((sum({$<Year = {$(v_ThisYear)},Month = , Loading = {'ID*'}>} TEU) ))))

This is my code to find 2012 expression, month is my dimension

Thinking of using current month minus previous month,

((sum({$<Year = {$(v_ThisYear)},Month = , Loading = {'ID*'}>} TEU) ))))

((sum({$<Year = {$(v_ThisYear)},Month = , Loading = {'ID*'}>} TEU) )))) ---> how do i derive previous month

Re: Month on Month?

Mon, 10 Jun 2013 02:43:44 GMT

Hi,

If its based on current month you can use this as basis..

For current month:

sum({<Month = ("$(=max(Month))")>}value)

For last month:

sum({<Month = ("$(=max(Month)-1)")>}value)

You can now have MOM change

Regards,

Janzen

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 02:51:31 GMT

Thanks for the reply

However, it is suppose to show 24months data comparison.

March 2011 Value Mar 2011-Feb 2011

April 2011 April 2011 - Mar 2011

May 2011 .

June 2011 .

.Dec 2011

Jan 2012 Jan 2012-Dec2011

Dec 2012

Re: Month on Month?

Mon, 10 Jun 2013 02:59:25 GMT

Hi,

You should put a value for month like this

Pass the value of month to a variable

v_ThisMonth = max({<Year = {$(v_ThisYear)} >} Month)

current month and year:

((sum({$<Year = {$(v_ThisYear)},Month = {$(v_ThisMonth)}, Loading = {'ID*'}>} TEU) ))))

current month last year:

((sum({$<Year = {$(v_LastYear)},Month = {$(v_ThisMonth)}, Loading = {'ID*'}>} TEU) ))))

Regards,

Janzen

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 03:26:50 GMT

Hi,

Tried but diden work out. However this code will only provide the month data?

Is that the comparison data value?

Re: Month on Month?

Mon, 10 Jun 2013 03:50:54 GMT

Hi,

Could you attach a sample file?

Regards,

Janzen

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 05:00:08 GMT

I will try to attach a sample file.meanwhile I am usinng if else statement to derive MOM

=if(Month = 'Feb',

(sum({$<Year = {$(v_ThisYear)},Month={'Feb'}, Unlocode_Port_Of_Loading = {'ID*'}>} TEU)
-
sum({$<Year = {$(v_ThisYear)},Month={'Jan'},Unlocode_Port_Of_Loading = {'ID*'}>} TEU)),

if(Month = 'Mar',

(sum({$<Year = {$(v_ThisYear)},Month={'Mar'}, Unlocode_Port_Of_Loading = {'ID*'}>} TEU)
-
sum({$<Year = {$(v_ThisYear)},Month={'Feb'},Unlocode_Port_Of_Loading = {'ID*'}>} TEU))

))

However, it is not working

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 06:24:53 GMT

tried using that but failed.

is there such a thing of column(2) - column(1)-1?

am totally clueless on this now.

unable to use if else statement too...

Re: Month on Month?

Gysbert_Wassenaar — Mon, 10 Jun 2013 06:34:57 GMT

If you're using Month as dimension you can't use set analysis (without making things very very complicated). You can use an AsOf table instead. See this document. In your case all you need is a table with the month and the previous month:

MonthAsOf	Month
Jan 2012	Dec 2011
Feb 2012	Jan 2012
...etc

Re: Month on Month?

n1ef5ng1 — Mon, 10 Jun 2013 07:15:39 GMT

my output is zero when i use that code.

below are my qv file,

Re: Month on Month?

n1ef5ng1 — Tue, 11 Jun 2013 00:44:02 GMT

any help

Re: Month on Month?

n1ef5ng1 — Tue, 11 Jun 2013 01:25:21 GMT

Hi,

I am using your method, bascially it MonthAsOf - Month to achieve the result?

However how do I create a new column for MonthAsOF

i am trying out by using Month -1 AS MonthAsOf