topic Set analysis substitute for the Above() in QlikView

Set analysis substitute for the Above()

Wed, 03 Mar 2010 22:15:42 GMT

Hi Guys,

I'm looking for a set analysis substitute for the Above() inter chart function.

Tasking as an example: Above(Sum (Sales))

In a one dimension (year) table like this:

YEAR SALES ABOVE

2006 506 -

2007 234 506

2008 456 234

2009 569 456

2010 889 569

The syntax I have in mind would look like this:

Sum({<Year = {$(=(num(Year) - 1)}>} Sales)

Of course this returns ZERO everywhere, since the values are excluded by the table dimension itself. What would be the best way to get around that?

Any hint in the right direction is appreciated. Thanks.

Set analysis substitute for the Above()

johnw — Wed, 03 Mar 2010 22:30:26 GMT

A set analysis set is evaluated once for the entire chart, not once for each line of the chart. You can't do this with set analysis.

I assume there's some reason you can't just use above()? There are other solutions, but above() is the easy one.

Set analysis substitute for the Above()

Thu, 04 Mar 2010 15:42:52 GMT

Hmm. Makes sense...

Well, as you guessed it, the idea is a little more complicated, this is just the last piece of the puzzle I need. Let me try to explain it in simple terms.

The idea is create a 2 dimensions pivot table which uses a rangeavg() of those sales ratios (year on previous year), and I have to be able to slice and dice it any direction, so script pre-aggr is out of the question...

Something along the lines of: rangeavg([2006 sales / 2005 sales], [2007 sales / 2006 sales], [2008 sales / 2007 sales]) * constant_XYZ = Value ABC. And I want to create a matrix of those "Value ABC" (in a pivot table).

Set analysis substitute for the Above()

Thu, 04 Mar 2010 16:32:02 GMT

I have gotten something like this to work before, but the whole thing has to be nested in two places; in the outer formula as well as the set definition. This may not work out of the gate, but if you play with it (specifically the level to which you nest in the set definition), it should work for you.

Sum(aggr(Sum({<Year = {'=aggr(num(Year) - 1, Year)'}>} Sales), Year))

Set analysis substitute for the Above()

Fri, 05 Mar 2010 23:36:12 GMT

Aaron, your reply seems insightful, but I just can't get the gispt of it...

But heh, in the mean time I found a way to make it work, by simply doing a one field data island representing the years, and build my PIVOT using the IslandYear field, coupled with set analysis only(IslandYear) - 1 statements.

In short, I'm just using the cartesian effect of unlinked field, coupled with dynamic set analysis expressions which reduce the data by using the isolated field. Works pretty good.

It seems that lately, I've been solving my challenges mainly with data islands, must be something to them...

Anyway, when in doubt: use a data island.

Cheers, and 'til next time, and thanks for playing guys.

Set analysis substitute for the Above()

amars — Wed, 09 Mar 2011 07:18:04 GMT

Hi,

You can use Total in ur expression, so because of the dimension it will never give zero but again it will give total for the year & the selection won't work in that case. And whatever condition u need to give needs to be give in the set Analysis expression.

Thanks