topic Re: Set analysis for cumulative sum in QlikView

Set analysis for cumulative sum

Wed, 19 Aug 2015 07:27:05 GMT

Hi guys,

Consider the following tables:

I am looking for a correct set analysis to sum up the amount spent by the progress date.

Say on 10-01-14 the amount spent should be the sum of expenditure starting 09-01-14. While the amount spent on 02-04-2014 should be the summation of expenditure before and until 02-04-14 (which is 09-01-14 + 02-04-14).

Can somebody show me the correct way to express this using set analysis?

Thank you very much for your kind attention guys.

Re: Set analysis for cumulative sum

Gysbert_Wassenaar — Wed, 19 Aug 2015 07:38:11 GMT

Try sum(aggr(rangesum(above(sum({<Date=>}Amount),0,rowno())),Date)

Re: Set analysis for cumulative sum

Wed, 19 Aug 2015 07:44:13 GMT

Gysbert,

Thank you very much for your kind attention. Got the amount displayed correctly!

Thank you again

Re: Set analysis for cumulative sum

Wed, 19 Aug 2015 07:54:51 GMT

Gysbert,

Sorry to disturb you again.

The set analysis is correct if Progress Date not selected.. but say I select Progress Date 21-05-14 the number should show the summation of previous expenditure of date from 09-01-14 till 21-05-14 but it only show the expenditure for date 21-05-14.

Can you suggest a way to rectify this?

Re: Set analysis for cumulative sum

Gysbert_Wassenaar — Wed, 19 Aug 2015 08:04:16 GMT

Try sum({<Date={"<=$(=max(Date))"}>}aggr(rangesum(above(sum({<Date=>}Amount),0,rowno())),Date)

Re: Set analysis for cumulative sum

Wed, 19 Aug 2015 08:27:42 GMT

Hi Gysbert,

Thank you for your kind attention but it seems to be the same.

Attached is the qvw file for your kind review.

Thank you again

Re: Set analysis for cumulative sum

Gysbert_Wassenaar — Wed, 19 Aug 2015 08:57:01 GMT

If you start using another date dimension then you need to change the expression to reflect that change.