How to write Set analysis to get YTD

[#1]

Hi All,

I have the following table:

Table1:

load * Inline

[YearMonth, Revenue

'2013/1/1',100

'2013/2/1',200

'2013/3/1',300

'2013/4/1',400

'2013/5/1',500

];

I want to use one Pivot table to show the YTD revenue, take example:

YearMonth,Revenue

'2013/1/1',100

'2013/2/1',300

'2013/3/1',600

'2013/4/1',1000

'2013/5/1',1500

I use this expression to finish, but it failed...

=sum({<YearMonth={'<=$(=Only(YearMonth))'}>}TOTAL Revenue)

How can I use Set analysis to achieve it?

Thanks.

[#2]

What I want is: If the user just chooses 2013-3-1 and 2013-4-1,  the pivot table should show 2 rows:  the YTD revenue to 2013-3-1and the YTD revenue to 2013-4-1.

[kedar_dandekar · Accepted solution]

Hi Issac,

Considering you want to evaluate your "Expression's - Set Analysis" in the context of the dimension,

a way to achieve it, would be by using an IF clause. Checkout the attached application.

If it meets your requirements, then please do refer to the following article,

it also details how you can form the expression dynamically:

Evaluating "sets" in the context of a dimension

HTH,

KD