Set Analysis

[samvile18]

Morning All,

Just a quickie:

I'm using the attached set analysis to calculate the Actual value for the year 2011. What I want to do is change it, so I can look at the latest months value instead. I'm guessing I need to do something with the max and month function but I can't quite figure it out.

Can anyone help me please?

Sum({$<Year = {2011},Measure={'Actual'}>}Value)

[martin59 · Accepted solution]

Hi,

If you want to work on the latest month, I advice you to do something like this :

Sum({$<Year = {2011},Measure={'Actual'}, Month={"$(=Max(Month))"} >}Value)

Or

Sum({$<MonthYear = {"$(=Max(MonthYear))"},Measure={'Actual'}} >}Value)

And if you want to work on the three latest months, you have to do :

Sum({$<MonthYear = {">=$(=AddMonths(Max(MonthYear),-2))"},Measure={'Actual'}} >}Value)

Hope that helps you

Martin

[lironbaram]

hei

try something like this

Sum({$<Year = {2011},Month={"$(=max(Month))"},Measure={'Actual'}>}Value)

assunming month is a numeric field

[samvile18]

That does work, I do however have a problem with my data which could be tricky to get around.

The max month will be 12 as there is budget data loaded until the end of the year, this set analysis therefore returns £0 for the actual data because there's only data loaded up Jul (7) for actuals.

Do you know a way around that?!