Using set analysis to return last year's value by month

[william_ericksson]

Hi everyone,

I've got a situation where I want to return the value of the previous year in a chart.

This is easy enough using above() function. However, I don't want to display the first year on my chart, so my selection is limited to 2015 onwards. The above() cannot refer to values that are not in the table.

Here is an example of my input and desired output.

I was thinking of using a set analysis to accomplish this, something like:

     sum(

            {<

                 Year = {"Year - 1"}

            >}

       Value)

But this doesn't work. I parameterised my year deduction and tried to insert it within $():

Variable:

     expYearMinusOne = '$1-1'

Then in the chart:

     =$(expYearMinusOne(Cal_Year))    returns the year minus one as expected.

However, this doesn't work within a set analysis, e.g.:

sum(

       {<

            Year = {'$(expYearMinusOne(Year))'}

       >}

  Value)

Any other ideas?

Thanks,

Will

[william_ericksson]

I should add that my final set analysis syntax was actually:

sum(

       {<

            Year = {"=$(expYearMinusOne(Year))"}

       >}

  Value)

[Anil_Babu_Samineni · Accepted solution]

May be this?

Dimension is Year

Current - Sum({<Year -= {'$(=Min(Year))'}>}Value)

Previous - Above(Sum(Value),1)

[william_ericksson]

Thank you Anil -your solution works well!

So in summary - rather than INCLUDE the years I want to see using set analysis, the solution is to EXCLUDE the years not wanted using -=

Brilliant!

[Anil_Babu_Samineni]

Awesome!! I wonder, you found something