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

[karawatts]

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

[Rohan]

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.

[karawatts]

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

[Rohan]

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.

[karawatts]

top 5 absolute values

[Rohan]

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.

[karawatts]

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

[Rohan]

Hi @karawatts ,

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

Regards,

Rohan.

[karawatts]

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