4 Replies Latest reply: May 20, 2011 8:13 AM by Grégory Nallet

# Problem with Set Analysis

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.

Thx,

Greg

• ###### Problem with Set Analysis

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

• ###### Problem with Set Analysis

Hi Greg,

Try below syntax.

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

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

Cheers.

• ###### Re: Problem with Set Analysis

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!

• ###### Problem with Set Analysis

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 ;-))