How to calcualte previous Month Year column with set analysis

[#1]

Greetings,

Question

I would like to create a pivot table with a column showing sales of a given month and another column showing the sales of the same month last year.

I would like to do this using expressions and not in the script.

Example

Lets say I have the following data (however there will be more dimensions like country, product etc.):

MonthYear	Sales
jan-12	100
feb-12	200
mar-12	300
apr-12	400
maj-12	500
jun-12	600
jul-12	700
aug-12	800
sep-12	900
okt-12	1000
nov-12	1100
dec-12	1200
jan-13	1300
feb-13	1400
mar-13	1500
apr-13	1600
maj-13	1700
jun-13	1800
jul-13	1900
aug-13	2000
sep-13	2100
okt-13	2200
nov-13	2300
dec-13	2400

This is what I would like to create:

MonthYear	Sales	Sales - Same month Last Year
jan-12	100	-
feb-12	200	-
mar-12	300	-
apr-12	400	-
maj-12	500	-
jun-12	600	-
jul-12	700	-
aug-12	800	-
sep-12	900	-
okt-12	1000	-
nov-12	1100	-
dec-12	1200	-
jan-13	1300	100
feb-13	1400	200
mar-13	1500	300
apr-13	1600	400
maj-13	1700	500
jun-13	1800	600
jul-13	1900	700
aug-13	2000	800
sep-13	2100	900
okt-13	2200	1000
nov-13	2300	1100
dec-13	2400	1200

What have been tried

I have tried several variations of set analysis with the general idea being:

=sum({<MonthYearNum={$(=num(Addmonths(MonthYearNum,-12)))}>} Amount)

However, I understand why this does not work, as the Dollar sign expansion do not take the table into account.

Does someone have any suggestiona?

Thank you.

[MarcoWedel]

Hi,

as you want to do this using a pivot table, another solution could be instead:

using two calculated dimensions (year and month):

tabSales:
LOAD Date#(MonthYear,'MMM-YY') as MonthYear, 
     Sales
FROM [http://community.qlik.com/thread/138534] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

[#3]

Dear Marco,

Thanks for taking the time to answer my question. I appreciate it.

It is a valid response given the information provided, but it is not exactly what I was looking for.

I have decided to use the AsOfTable approach (http://community.qlik.com/docs/DOC-4252) even though I at first did not want to use scripting.

Thanks again.

Best Regards

[MarcoWedel]

you're welcome

regards

Marco