How to create a reverse cumulative column pivot (groups)?

[mrichman]

Hi Community,

I'm wondering on how you would solve this issue. I have currently a pivot in QlikView and I need a reverse cumulative column.

I only tried to make an example in excel, hopefully this helps.

Thanks in advance.

[Anonymous]

Hello, M Richman!

Try this:

Data:

Load * Inline

[Field, Stage, Team

3, Stage 1, Team A

2, Stage 2, Team A

5, Stage 3, Team A

3, Stage 1, Team B

3, Stage 2, Team B

3, Stage 3, Team B

3, Stage 1, Team C

1, Stage 2, Team C

3, Stage 3, Team C

];

FinalData:

Load *,

If(Previous(Team) = Team, Field + Peek('Accumulative'), Field) As Accumulative

Resident Data

Order By Team, Stage desc;

Drop Table Data;

[mrichman]

Hi Daniel,

Thanks for your message, how would implement this in an expression?

Thanks in advance!

[vishsaggi]

Not quiet sure what your expected output is. Can you elaborate a little? And how do you identify if the totals are reverse cumulative i mean how do you differentiate?

[sunny_talwar · Accepted solution]

May be this

Sum(TOTAL <Team, Week> Sales) - RangeSum(Above(Sum(Sales), 1, RowNo()))

[mrichman]

Thanks again Sunny! As always!

[mrichman]

Hi Sunny,

Quick question relating to your answer.. if I select a range of date i.e. 21/5, 22/5, 23/5 and so on, the first date will always will be blank. The logic is that it cannot compare as it has not field to compare values. But, is there any other formula which will make this is possible? If I would select a range of dates Monday to Friday..... I would like that Monday will be compared to Friday last week.

Thanks in advance!

[sunny_talwar]

Suggest you to look into The As-Of Table