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

1 Solution

Accepted Solutions
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

View solution in original post

20 Replies
sunny_talwar

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.

What does this mean? What is the desired output based on selections and when nothing is selected?

sureshbaabu
Creator III
Creator III
Author

Please refer the attached.

I would like to retain the Cumulative sum that was initially derived, even when a selection is made on the column 'Group'. But Currently, the Cumulative sum changes as I make a selection on the column Group.

Issue.jpg

sunny_talwar

Check now

sureshbaabu
Creator III
Creator III
Author

Thank you!! that worked as expected.

Could you please share your thoughts on the below -

1. If a resource does not have an entry for a month. the expression you suggested retuns null, but it should be showing up the cumulative value

Note: I removed the record with period '01/03/2017' for Resource5, from the data file shared initially

2. the total does not populate for cumulative.

issue 2.jpg

sunny_talwar

1) Would you be able to share a sample where you modified the data to have null?

2) What do you expect a total of cumulative to be? the last value or something else?

sureshbaabu
Creator III
Creator III
Author

1. I have uploaded the new qvw to the discussion. please check.

2. Cumuliative of all the resources by Month (the same way on how the total of actual is calualted)

sunny_talwar

Here is the solution to your first issue

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

/(rangesum(Before(sum([Hours Billed]),0,RowNo())))*avg(TOTAL <[Resource Name]>1)

2) I am still not sure what you mean, what numbers do you expect to see for the sample? Logic can you explain the logic also?

sureshbaabu
Creator III
Creator III
Author

Thanks for the solution. That's cool

For the second issue:

I have uploaded the new file.Please check the cumulative total on the chart 'Month View'.


Jan-> 70%

Feb->76%

Mar->77%

Thank you!!

sunny_talwar

One solution

=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))


Capture.PNG