Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator
Creator

How to calculate cumulative sum in a pivot table

Use

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

How to calculate cumulative sum in a pivot table

Hi,

   Try this,

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

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

How to calculate cumulative sum in a pivot table

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

Highlighted
Creator
Creator

How to calculate cumulative sum in a pivot table

Use

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

View solution in original post

Highlighted
Contributor III
Contributor III

How to calculate cumulative sum in a pivot table

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

Highlighted
Contributor III
Contributor III

How to calculate cumulative sum in a pivot table

Thanks Jedson....

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