topic Re: Problem with Set Analysis in QlikView

Problem with Set Analysis

Thu, 19 May 2011 13:03:46 GMT

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

Problem with Set Analysis

erichshiino — Thu, 19 May 2011 13:14:48 GMT

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

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

Regards,

Erich

Problem with Set Analysis

Thu, 19 May 2011 13:45:50 GMT

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

Thu, 19 May 2011 15:52:47 GMT

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

Fri, 20 May 2011 12:13:07 GMT

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