Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I was just trying to calculate cumulative sum in a pivot table. But I am not able to get the exact values when I used multiple dimension...
Please suggest me any solution if anyone of you gone through the same problem.
Suppose I have following table structure,
Product SubProduct Sum(Sales)
A aa1 10
A aa2 20
A aa3 30
B bb1 40
B bb2 50
B bb3 60
B bb4 70
C cc1 80
C cc2 90
I want to display the same in Pivot table as,
Product SubProduct Sum(Sales) Cummulative Sum
A aa1 10 10
A aa2 20 30
A aa3 30 60
B bb1 40 100
B bb2 50 150
B bb3 60 210
B bb4 70 280
C cc1 80 360
C cc2 90 450
Please suggest any appropriate solution...
Thanks.
Hi,
Try this,
above(sum(Sales)) +sum(Sales)
Regards,
Kaushik Solanki
you could use this:
LOAD *, RANGESUM(PEEK('CSum'),Sales) as CSum INLINE [
Product, SubProduct, Sales
A, aa1, 10
A, aa2, 20
A, aa3, 30
B, bb1, 40
B, bb2, 50
B, bb3, 60
B, bb4, 70
C, cc1, 80
C, cc2, 90
];
The Cummulative Sum is in field CSum
Hope help you
Use
rangesum(above(total sum(Sales), 0, rowno(Total)))
Thanks for your valuable inputs Kaushik n TheFourth...
@Thefourth, the script you have provided is working fine...thank you so much...
But is there any way to show the cumulative some on Report using some expression...
i.e not it edit script...is it possible to write some expression directly in chart(Pivot) expression...
The Cumulative sum changes that changes as per the selection made in dimension....
Please provide your comments on that....
once again thanks a lot again....:)
Thanks Jedson....
Yeah its working fine...yuppie....