Pivot-Calculation baesd on calculated previous value

[marcel_h]

Hello,

I challenge to compute a value in a pivot table based on each month and its pre-value.

I have tried a few with rangesum () and before (), but I just can not get the result you want.

Condition:

I have a pivot table. In the columns I have the months 1-12. Then I have a selective metric for each month. The recalculated measure is based on the corresponding month and the sum of the calculated previous values.

1. Example value for month 1 is the original value.

2. Value for month 2 is "original value * 2 - sum(calculated pre-values)."

3. Value for month 3 is "original value * 3 - sum(calculated pre-values)".

4. ......

In the example above, I've built my own formula for each month in each row. But i want to show the values in one row and without this temporary rows.

v1 = 
if(Monat=1 ,column(1) )

v2 = 
if(Monat=2
	,column(1) * Monat - before(column(1))
    )

v3 = 
if(Monat=3
	,column(1) * Monat - rangesum(before(column(2),2), before(column(3),1))
    )

v4 =
if(Monat=4
	,column(1) * Monat - rangesum(before(column(2),3), before(column(3),2), before(column(4),1))
    )

I hope that it was understandable and someone can help me.

Thanks in advance.

mfg

Marcel

[ChristofSchwarz]

In your example the numbers from the "stairs" pivot are not correct, they don't show the result which you added in red text .... the 4th column should be 5.05% ...

---

However, the formula you need is this ... replace Sum(Value) with whatever your formula is

Sum(Value)*Monat - RangeSum(Before(Sum(Value), 1, ColumnNo()))

The part you missed is that Before can not only return one value, but with a 2nd and 3rd parameter of the function it can (like Excel) return a whole array, for which you need a RangeSum() around it to build the total. The 2nd gives the offset where to start (1 means begin with the next column to the left) and the 3rd argument defines the number of cells to go to the left. So if you are at column 4, you needed this: Before(Sum(Value), 1, 4) ... To make the 3rd parameter dynamic, I put in the function ColumnNo()

[marcel_h]

Hello,
many thanks for the answer.

Exactly this formula I have already developed, but that is not the desired result. The correct result for value(4) is 5.22.
Maybe the logic at the value 5 becomes a little clearer. Here I would expect now as follows.
Original value (5) * Month (5) minus the sum of the previous calculated values.

So:

Value5 = 6.31 * 5- (6.54 + 7.66 + 6.28 + 5.66)

And this is where your value deviates even more from the desired result.
The problem is that the formula in principle refers to itself, so a circular reference. In Excel you can solve this great, because it is cell based.

Here are the correctly calculated results. I do not know how to attach an Excel file here, otherwise I would have done that.

I have already tried using an if-formula every month. But at month 10 from 40,000 characters Qlik Sense did not join in then 🙂 That was really just an experiment for a stopgap. 

mfg

Marcel

[ChristofSchwarz · Accepted solution]

Same can be done in Qlik ....

Sum(Value)*Monat - RangeSum(Before(Column(1), 1, ColumnNo()))

I don't have your percentage values at a 2 digit precision, but from a first look, they are now identical.

PS contact me at csw@qlik.com if this still doesn't solve it, so we can exchange files or have a screen sharing session

[marcel_h]

Hi,
thank you very much. 

It worked 🙂

mfg

Marcel