topic Re: Pivot-Calculation baesd on calculated previous value in App Development

Pivot-Calculation baesd on calculated previous value

marcel_h — Sat, 16 Nov 2024 06:19:43 GMT

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

Re: Pivot-Calculation baesd on calculated previous value

ChristofSchwarz — Fri, 15 Mar 2019 20:50:40 GMT

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()

Re: Pivot-Calculation baesd on calculated previous value

marcel_h — Fri, 15 Mar 2019 22:15:02 GMT

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

Re: Pivot-Calculation baesd on calculated previous value

ChristofSchwarz — Sat, 16 Mar 2019 09:36:11 GMT

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

Re: Pivot-Calculation baesd on calculated previous value

marcel_h — Sat, 16 Mar 2019 10:05:12 GMT

Hi,
thank you very much.

It worked 🙂

mfg

Marcel