Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
prasannarc_jbs2
Contributor III
Contributor III

How to calculate cumulative sum in a pivot table

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.

1 Solution

Accepted Solutions
jedgson
Creator
Creator

Use

rangesum(above(total sum(Sales), 0, rowno(Total)))

View solution in original post

5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   Try this,

   above(sum(Sales)) +sum(Sales)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable

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

jedgson
Creator
Creator

Use

rangesum(above(total sum(Sales), 0, rowno(Total)))

prasannarc_jbs2
Contributor III
Contributor III
Author

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

prasannarc_jbs2
Contributor III
Contributor III
Author

Thanks Jedson....

Yeah its working fine...yuppie....