Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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).
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))
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.
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