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.

[cesaraccardi]

Hi Isaac,

I think the issue is with the Date format that you have chosen, it is probably dividing 2013 by 5 then by 1 instead of comparing with the full date "2013/5/1". You could either change the format to DD/MM/YYYY or try to apply a formatting function in your expression, something like:

=sum({<YearMonth={"<=$(=Date#(Only(YearMonth),'YYYY/M/D'))"}>}TOTAL Revenue)

Kind Regards,

Cesar

[#3]

Thanks for your reply, but it does not work, the problem I know is the YearMonth can be captured if we select one option in YearMonth listbox. But if we don't choose any option in YearMonth, the expression does not work.

So the key point is how can we capture the current YearMonth for each row in Pivot table?

Thanks.

[cesaraccardi]

Isaac,

If you add the expression "Only(YearMonth)" you should see the current YearMonth for each row (if you have YearMonth as a dimension or another field in the same level of aggregation). If that's true the issue is related to the comparison <= and the date format.

Are you able to post the application so I can have a look?

Kind Regards,

Cesar

[abeyphilip]

Hi,

If you are fine with using a straight table and no set analysis.

Regards,

Abey

[#6]

It is enclosed...

[#7]

Thanks. but the my question has to be solved by Set analysis.

[cesaraccardi]

Hi Isaac,

I see what you mean now... Please have a look.

[#9]

Yes, it is close to what I wanted. However, the report you provided: the YearMonth listbox does not work, how can the user chooses the YearMonth and also show the right YTD revenue?

[cesaraccardi]

Yes, the way I did will always display all the MonthYear values in the table regardless of selections. What is your requirement? Do you need to show all months or only the latest one in the table?