Problem with Set Analysis

[#1]

Hi,

I have a problem with Set analysis.

I try to calculate, in a pivot table, the sum of costs values (CO_INT_ACTUAL) for the selected year (Y), for the next year (Y+1), and for all the years after.

My totals for columns "selected year" and "next year" and next year works using this formula.

=Sum({$ <Year={$(=Only(Year)) [CO_INT_ACTUAL] )

To calculate the sum for all the years after, i use this formula:

=Sum({$<Year={$(>Only(Year+1))}>} [CO_INT_ACTUAL])

But it doesn't work.

Can anybody help me please?

Thx,

Greg

[erichshiino]

Hi,

Not sure if I understood the dynamic of the user selections in your case,

but from a syntax point of view, your expression should be more like this:

=Sum({$<Year={">$(Only(Year)+1)"}>} [CO_INT_ACTUAL])

or

=Sum({$<Year={">$(=Only(Year)+1)"}>} [CO_INT_ACTUAL])

Regards,

Erich

[#3 · Accepted solution]

Hi Greg,

Try below syntax.

sum({<Year={$(=only(Year))}>}[CO_INT_ACTUAL])

sum({<Year={'>$(=only(Year)+1)'}>}[CO_INT_ACTUAL])

Cheers.

[#4]

Do not use set analysis, use a calculated dimension and a variable containing the base year.

I would do this:

1 - create a variable vBaseYear

2 - create an input box for the variable

3 - create a chart calculated dimension as this (untested, check the parens):

          =If(Year = vBaseYear, 'Y', If(Year = vBaseYear + 1, 'Y+1', If(Year > vBaseYear + 1, 'After', Null())))

This should give you a dimension containing 'Y' for rows pertaining vBaseYear, 'Y+1' for the next one and 'After' for years in the future.

Selecting 'Ignore if value is null' will delete every row associated with years < vBaseYear.

Good luck!

[#5]

Hi Shyam,

thx a lot for the good Syntax. In my Set Analysis User Guide, there are no quotes in the formula...

Thanks Chiesa and Erich for your answers (i didn't need to test it but they are probably good too).

Bye ;-))