Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis substitute for the Above()

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.

5 Replies
johnw
Champion III
Champion III

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

amars
Specialist
Specialist

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