Set analysis for cumulative sum

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

Gysbert,

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

Thank you again

[#4]

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?

[Gysbert_Wassenaar]

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

[#6]

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

[Gysbert_Wassenaar]

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