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: 
sureshbaabu
Creator III
Creator III

Issue with cumulative Sum

Hello All,

I'm having an issue while calculating the cumulative sum.

I was able to create the cumulative% and the results are as expected when there is no selection made.

however, on any selection the cumulative  value changes.

I believe the issue may have occurred since I use a 'Before' function in the calculation. As the number of records comes down when a selection is made.



In my attached example, I have built a chart with actual and cumulative.

wanted to make a selection on the group, and that should not be the cumulative value, but filter the resources associated with the group.

Could someone please share your thoughts?

Thanks

20 Replies
sureshbaabu
Creator III
Creator III
Author

Thank you so much!! I'm getting closer to the fix.

I tried to implement the same logic with the original data set but the cumulative value seems to be not matching.

I have another filler on the set expression. Could you please check the attached?

Thanks

sunny_talwar

Load the new data to the existing qvw? What is the expected output now?

sureshbaabu
Creator III
Creator III
Author

I thought I attached the app earlier. Please find them here.

The expected cumulative total is on the 'Month View' Chart.

sunny_talwar

Do you have QV12? I really hope you do, because it seems that Period isn't correctly sorted in ascending order in the script which is causing the RangeSum(Above()) to not accumulating in the right manner. I have modified the expression to use this new concept which is available in QV12 (The sortable Aggr function is finally here!‌). If in case you don't have QV12, you will have to make sure to sort period in the script to make this to work.....

=If(Dimensionality() = 2,

(rangesum(Before(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))

/(rangesum(Before(sum([Hours Billed]),0,RowNo()))),

Aggr((rangesum(Above(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))/(rangesum(Above(sum([Hours Billed]),0,RowNo()))), (Period,(NUMERIC))))

Capture.PNG

sureshbaabu
Creator III
Creator III
Author

Thank you so much for your time. I will try to apply this on the main application and share my observations. Thanks Again for the help.

sureshbaabu
Creator III
Creator III
Author

I"m using v12 on my desktop and the results are as expected. But the server is on 11.2 SR12. so, when the data is loaded it shows null ( - ) for the cumulative row.

Thanks

sunny_talwar

So then you need to fix the sorting in the script... if you don't want to move too many things around, you can do this...

SortTable:

LOAD Distinct Period,

  AutoNumber(Period) as Sort

Resident Sheet1$;

Replace Sheet1$ this with the table where Period field resides (in your sample the table name is Sheet1$)

Now use Sort in your aggr in place of Period

=If(Dimensionality() = 2,

(rangesum(Before(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))

/(rangesum(Before(sum([Hours Billed]),0,RowNo()))),

Aggr((rangesum(Above(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))/(rangesum(Above(sum([Hours Billed]),0,RowNo()))), Sort))

sureshbaabu
Creator III
Creator III
Author

Thanks for the response.

Yeah... I was trying to implement the same as well as suggested earlier. The cum does not match though.

Attached is the updated application.

sunny_talwar

Sorry, I gave you the wrong table code -> Forgot to add Order By statement

SortTable:

LOAD Distinct Period,

  AutoNumber(Period) as Sort

Resident Source

Order By Period;

Can you try now

sureshbaabu
Creator III
Creator III
Author

Thank you Sunny!! it worked.