Using set analysis to cummulate values.

[#1]

What I would like to achieve is to cumulate values based upon a calculated date, not using Rangesum, Before or Above. I have it working with If within my sum but I would like to see it working using set analysis. Mainly for me to better understand the working of set analysis.

This is the simplified situation:

I have various values registered on various dates:

MyData:
load * inline [
MyKey, MyDate, MyValue
1, 01/01/2013, 5
2, 02/01/2013, 10
3, 02/02/2013, 20
4, 05/02/2013, 30
];

I would like to see the cumulative value of all values at the end of each month.  Creating a pivot and using RangeSum(above(sum(MyValue),0,RowNo()))  works but as soon as I start to only select a certain month I lose my above data and thus is my data invalid.

Since I just want to view the situation at the end of e.g. feb I have created a Detached Calendar allowing me to select dates without influencing the selection in the MyData table.

DetachedCalendar:
load * inline [
DetachedMonthName, DetachedMonth
'Jan', 1
'Feb', 2
];

Now I create a new pivot, put the detached month in the dimension and build the following expressions.

Last Day of month (works):

Date(MonthsEnd(1,MakeDate(2013,DetachedMonth)),'DD/MM/YYYY')

Cummulative using IF (works)

sum(if(MyDate<=MonthsEnd(1,MakeDate(2013,DetachedMonth)),MyValue))

Cummulative using SET (not working)

sum({<MyDate={"<=$(Date(MonthsEnd(1,MakeDate(2013,DetachedMonth)),'DD/MM/YYYY'))"}>} MyValue)

I have attached the example to this post.

So my question is, can this be done using set analysis? And if so what am I doing wrong?

Thanks for your input.

Regards,

Jeroen

[Gysbert_Wassenaar · Accepted solution]

Set analysis won't work because the set is calculated per chart, not per row. Since you use month as a dimension you want the value of the month to determine the set. And as said that's not possible with set analysis. The if statement does work because that is evaluated per row. If the if statement turns out to give performance problems maybe you could create the cumulative sum in the script.

[#3]

Gysbert,

The if indeed gave me some performance problems in the past, the cummulative sum in the script works as well, only downside is that your data files grow and grow using that strategy.

Anyways thanks for your answer, I know what to do now and won't stare blind on the set analysis for this situation.

Thanks,

Jeroen