Issue with cumulative Sum

[sureshbaabu]

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

[sureshbaabu]

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]

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

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

[sunny_talwar · Accepted solution]

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

[sureshbaabu]

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]

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]

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]

Thank you Sunny!! it worked.