Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Contributor III

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

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

1 Solution

Accepted Solutions

May be this?

Dimension is Year

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

Previous - Above(Sum(Value),1)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
4 Replies
Contributor III
Author

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

sum(

{<

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

>}

Value)

May be this?

Dimension is Year

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

Previous - Above(Sum(Value),1)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor III
Author

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!

Awesome!! I wonder, you found something

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Community Browser