"Set analysis" in a chart/pivot

[#1]

I have a dataset that looks like this:

Month	Sales Team	Sales rep	Sum
1	A	X	400
1	A	Y	200
1	B	Z	300
1	B	Q	500
2	A	X	300
2	A	Y	400
2	B	Z	600
2	B	Q	700
3	A	X	800
3	A	Y	100
3	B	Z	90
3	B	Q	5

for each row I want to show the sum of the previous month, for that sales rep and sales team.

so it would look like this:

Month	Sales Team	Sales rep	Sum	Sum last month
1	A	X	400	300
1	A	Y	200	400
1	B	Z	300	600
1	B	Q	500	700
2	A	X	300	800
2	A	Y	400	100
2	B	Z	600	90
2	B	Q	700	5
3	A	X	800	(and so on)
3	A	Y	100
3	B	Z	90
3	B	Q	5

the real data is ofcourse more complicated and a few more dimensions.. and needs to work in a pivot not just straight table.

I've tried twenty different set-analysis ideá (above/below does not work in a pivot where user can open and close dimensions)

[stephenedberkg]

if you are using master calendar try this

=sum({< Years={$(=Max(Years))},MonthId={$(=Max(MonthId) -1)}>}Sales)

[#3]

Problem is Max(MonthID) -1 only works for the last month

I want this to work over several months (or even years)

I have all the months in the report already mapped up with an "ID" from 1 to 99 so year thing is not needed.

what I'm after is a generic expression like:

sum(Sales) where MonthID = MonthID-1

[marcus_sommer]

The open/close option could be disabled (tab presentation) and so above() will work. Otherwise I think you will need for each month an own expression-column and to remove the month-dimension from the pivot.

- Marcus

[Oleg_Troyansky · Accepted solution]

For a universal solution that could work in any chart and any calculation, you need to create the "As of Date" table, where you can define relationships between the current month and the prior month using Flags. Then, the calculation becomes rather simple.

You can find several free sources that describe how to build and use the "As of Date" table, including my blog article:

QlikView Blog Q-Tip #4 How to Use "As of Date" table | Natural Synergies

If you'd like a bit deeper coverage, I'd recommend to read about it in my new book QlikView Your Business. We also teach this and many other advanced techniques at the Masters Summit for Qlik.

Cheers,

Oleg Troyansky

[#6]

Thank Oleg. Did some small scale test and it works like a charm.

Was begining to think Id have to reload all the data for each month as "previous months amount".

[stephenedberkg]

try this

=sum({< Years={$(=Max(Years))},MonthId={$(=Max(MonthId) -1)},Month=>}Sales)