topic Re: How to create a reverse cumulative column pivot (groups)? in QlikView

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

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

Anonymous — Thu, 07 Jun 2018 15:12:27 GMT

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;

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

mrichman — Thu, 07 Jun 2018 15:18:07 GMT

Hi Daniel,

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

Thanks in advance!

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

vishsaggi — Thu, 07 Jun 2018 15:31:57 GMT

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?

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

sunny_talwar — Thu, 07 Jun 2018 15:45:10 GMT

May be this

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

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

mrichman — Thu, 07 Jun 2018 17:34:16 GMT

Thanks again Sunny! As always!

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

mrichman — Tue, 12 Jun 2018 17:13:41 GMT

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!

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

sunny_talwar — Tue, 12 Jun 2018 17:23:35 GMT

Suggest you to look into The As-Of Table