topic Re: Issue with cumulative Sum in QlikView

Issue with cumulative Sum

sureshbaabu — Wed, 29 Mar 2017 15:28:08 GMT

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

Re: Issue with cumulative Sum

sunny_talwar — Wed, 29 Mar 2017 15:35:31 GMT

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?

Re: Issue with cumulative Sum

sureshbaabu — Wed, 29 Mar 2017 17:09:24 GMT

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.

Re: Issue with cumulative Sum

sunny_talwar — Wed, 29 Mar 2017 17:29:26 GMT

Check now

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 08:40:25 GMT

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.

Re: Issue with cumulative Sum

sunny_talwar — Thu, 30 Mar 2017 08:42:55 GMT

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?

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 09:00:33 GMT

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)

Re: Issue with cumulative Sum

sunny_talwar — Thu, 30 Mar 2017 10:53:27 GMT

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?

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 13:13:32 GMT

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

Re: Issue with cumulative Sum

sunny_talwar — Thu, 30 Mar 2017 13:25:37 GMT

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

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 15:38:28 GMT

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

Re: Issue with cumulative Sum

sunny_talwar — Thu, 30 Mar 2017 15:41:33 GMT

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

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 17:24:58 GMT

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

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

Re: Issue with cumulative Sum

sunny_talwar — Thu, 30 Mar 2017 17:34:26 GMT

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

Re: Issue with cumulative Sum

sureshbaabu — Thu, 30 Mar 2017 17:56:32 GMT

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.

Re: Issue with cumulative Sum

sureshbaabu — Fri, 31 Mar 2017 13:11:53 GMT

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

Re: Issue with cumulative Sum

sunny_talwar — Fri, 31 Mar 2017 13:16:10 GMT

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

Re: Issue with cumulative Sum

sureshbaabu — Fri, 31 Mar 2017 15:33:51 GMT

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.

Re: Issue with cumulative Sum

sunny_talwar — Fri, 31 Mar 2017 15:36:11 GMT

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

Re: Issue with cumulative Sum

sureshbaabu — Wed, 05 Apr 2017 06:41:22 GMT

Thank you Sunny!! it worked.