
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Final/Max value of Cumulative sum of data in one column of pivot table expression and use the final value of cumulative sum in another expression
I have a pivot table where i have absolute cumulative sum of the rows and use the sum of rows (absolute value) in another expression field. This i want for top 50 values of the table.
the formula i have used to find total cumulative isn't working when i apply the top 50 dimension filter on qlikview (it picks the total of all trades from the backend files).
Formulas used:
Cumulative sum: rangesum(above(TOTAL fabs([column A],0,rowno(TOTAL)))
Final/Total value of cumulative sum: sum(TOTAL <A> fabs(aggr(sum(a),$Dimension)))
Normal Scenario |
A |
Absolute of Cumulative sum of A |
Total of cumulative sum |
Percentage(Cumulative sum / total *100) |
|
|
10 |
10 |
200 |
5 |
|
|
20 |
30 |
200 |
15 |
|
|
30 |
60 |
200 |
30 |
|
|
-20 |
80 |
200 |
40 |
|
|
50 |
130 |
200 |
65 |
|
|
-10 |
140 |
200 |
70 |
|
|
60 |
200 |
200 |
100 |
|
|
|
|
|
|
|
For Top 5 |
A |
Absolute of Cumulative sum of A |
Total of cumulative sum |
Percentage(Cumulative sum / total *100) |
|
|
10 |
10 |
130 |
.. |
|
|
20 |
30 |
130 |
.. |
|
|
30 |
60 |
130 |
.. |
|
|
-20 |
80 |
130 |
.. |
|
|
50 |
130 |
130 |
100 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Just create another field in the back end for A like :
if(A<0,A*-1,A) as NewA;
& then use this NewA in you expressions where you need Absolute values. you can use this expression in the front end as well but it will hamper front end calculations.
Regards,
Rohan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Rohan,
the formula i have used to find total cumulative isn't working when i apply the top 5 dimension filter on qlikview (it picks the total of all trades from the backend files).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Kara,
By Top 5 do you mean the ones with the top 5 absolute values in A or the 1st 5 records for A ?
Regards,
Rohan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
top 5 absolute values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
By which dimension are you trying to pick the top 5 values for A, like Salesperson, City, State or what ?
Try to put that dimension in Total <Dimension> & check once.
Regards,
Rohan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Let's say i sort it by Salesperson and the values i have given above are after sorting it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i have updated the post with the formulas that i use, do let me know how to find the final/total value of cumulative sum column
