Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
marcel_h
New Contributor

Pivot-Calculation baesd on calculated previous value

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. ......

2019-03-15_08h30_28.png

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

1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Pivot-Calculation baesd on calculated previous value

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.

2019-03-16 10_35_49-Window.png

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

View solution in original post

4 Replies
Highlighted
Employee
Employee

Re: Pivot-Calculation baesd on calculated previous value

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% ...

2019-03-15 21_45_55-Window.png

---

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

2019-03-15 21_50_13-Window.png

Highlighted
marcel_h
New Contributor

Re: Pivot-Calculation baesd on calculated previous value

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.

2019-03-15_23h05_14.png

2019-03-15_23h12_17.png

2019-03-15_23h12_29.png


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. 2019-03-15_23h10_14.png

mfg

Marcel

Highlighted
Employee
Employee

Re: Pivot-Calculation baesd on calculated previous value

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.

2019-03-16 10_35_49-Window.png

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

View solution in original post

Highlighted
marcel_h
New Contributor

Re: Pivot-Calculation baesd on calculated previous value

Hi,
thank you very much. 

It worked 🙂

mfg

Marcel