Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III

Cumulative Sum in QlikSense Expression using RangeSum and Above

Hi everybody...I am having some issues calculating a cumulative sum by Dimension type. I have looked in several forums but still haven't made it. 

I have the following table:

year   |  Company  |  Sales

2014  |           A          |    100

2015  |           A          |    50

2016  |           A          |    200

2014  |           B          |    10

2015  |           B          |    500

2016  |           B          |    500

 

And I need to get the cumulative sum for each company; getting the following result table:

year   |  Company  |  Sales   |  Cumulative Sum

2014  |           A          |    100     |      100

2015  |           A          |    50        |     150

2016  |           A          |    200     |      350

2014  |           B          |    10       |      10

2015  |           B          |    500     |     510

2016  |           B          |    500     |      1010

So as you can see, I have to sum the value of sales for each year by each Company. So far the closest I have gotten to getting my results is this expression: Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())),Company,Year)

This starts fine but does weird stuff as the table grows...please help!

I have been told to specifically use RangeSum ..

2 Solutions

Accepted Solutions
Taoufiq_Zarra

Maye be

in expression :

Rangesum(Above(sum(Sales), 0, RowNo()))

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

sunny_talwar

If you have to use Aggr() for some reason, you can try this

Aggr(
  RangeSum(Above(Sum(Sales), 0, RowNo()))
, Company, (Year, (NUMERIC)))

View solution in original post

3 Replies
Taoufiq_Zarra

Maye be

in expression :

Rangesum(Above(sum(Sales), 0, RowNo()))

 

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
sunny_talwar

If you have to use Aggr() for some reason, you can try this

Aggr(
  RangeSum(Above(Sum(Sales), 0, RowNo()))
, Company, (Year, (NUMERIC)))
EugeniaCastillaFragoso
Partner - Contributor III
Partner - Contributor III
Author

thank you so much, both of these answers work perfectly...I guess I will try to understand why now, so I am going back to the documentation. Thank you both for your help!