Announcements
cancel
Showing results for
Did you mean:
Contributor

## 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
Labels (12)

• ### Visualization

8 Replies
Specialist

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.

Contributor
Author

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

Specialist

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.

Contributor
Author

top 5 absolute values

Specialist

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.

Contributor
Author

Let's say i sort it by Salesperson and the values i have given above are after sorting it.

Specialist

Hi @karawatts ,

Then use that in Total like Total <SalesPerson> & test it out.

Regards,

Rohan.

Contributor
Author

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