Iterative Year vs Prior Year

[Anonymous]

I have a scenario.      In the sample table below, I need to show the  sales by year…    And for each year, I need to show the last yr and last 2nd year sales for that year.

For example in 2014,

Current Year = 2014 Sales

Last Year = 2013 Sales

Current Year = 2013 Sales

Last Year = 2012 Sales

 |----------2013------------|---------2014-------------|

 | Last Year | Current Year | Last Year | Current Year | 

Ive tried but when i nest them under a year dimension.. the calculations are not working..

is there a way around this, to come up with this kind of report format? our user is very particular in having such format..

We are using an existing model and are not allowed to do any modifications so are options are limited to report calculations, expression or set analysis..

many thanks for the help.

[ashfaq_haseeb]

Hi,

Have a look at attached application.

Regards

ASHFAQ

[#3]

You can do this by using set analysis, for current year sales

sum({<Year={'$(=Max(Year))'}>}sales)

and for previous year sales

sum(<Year={'$(=Max(Year)-1)'}>sales)

hope this will help you..

regards

bhawna

[Anonymous]

hi asfhaq,, thanks for it... very interesting,, i used it a reference..

Hi Bhawna,

   thanks for this.. This calculation is working... but when i nest them under a year dimension, they no longer work...

   is there a way to revise this calculation where in it will pickup the iterate value of the year column on top of it.. and use that to compute for the current and previous yr... ??

    many thanks!