Issue with set analysis

[#1]

Hi all,

I am trying to create a chart that shows a yearly trend and for that I am using set analysis on the year:

sum({$<Year=>}_profit)

/ sum({$<Year=>}_assets)

But I have an issue with creating the correct denominator. In business terms the correct calculation is:

(Assets current year + Assets last year)/2

For the yearly trend this means:

2009: (assets09 + assets08)/2

2010: (assets10 + assets09)/2

2011: (assets11 + assets10)/2

Any ideas, how to set up the expression, so that I can show the yearly trends for above?

Thanks for your help!

[jvitantonio]

Use:

(sum({$<Year={'2009'}>}_assets)+sum({$<Year={'2010'}>}_assets))/2

[swuehl · Accepted solution]

If your dimension is year, try using one of the chart inter record functions, above() or below() (depending on your sort order) or before() and after() (if dimension is pivoted in a pivot table):

sum({$<Year=>}_profit)

/ (sum({$<Year=>}_assets) + above(sum({$<Year=>}_assets)) )

[#4]

Thanks!