
Re: Complicated Top 'x' Dimension
Michael Steedle Apr 26, 2012 9:17 PM (in response to David Bonnet)To accomplish what you are looking for, you need to evaluate the Gross Margin threshold before doing the ranking at all; otherwise you will only get Dimension1 values that meet both criteria (what your AND is doing now).
Neither approach is particularly efficient, but have you considered moving that logic to the expressions instead of the dimensions? Pivot tables are already resourceintensive enough before introducing calculated dimensions, especially involving the aggr function. Something like this:
If(Sum(TOTAL <Dimension1> Margin) > 1000,
If(Rank(Sum(TOTAL <Dimension1> Units),4,1) < 11,
Sum(Margin)
)
)
It could likely be accomplished using P() in set analysis, but it wouldn't be much prettier.

Complicated Top 'x' Dimension
Oleg Troyansky Apr 26, 2012 9:23 PM (in response to David Bonnet)The problem here is that you validate two separate conditions  the Rank and the Margin. Only 2 out of the top 10 Dimensions can show Gross Margin over 1000. Assuming that you have more "dimension" values with high margins, your goal is to only include those values in your Ranking that satisfy the Margin requirements.
Try the following. This has not been tested, so use it "as is":
=aggr(if(rank(sum({< Dimension1 = {"=sum([Gross Margin]) >1000"} >} Units),4,1) < 11 ,Dimension1),Dimension1)
The Set Analysis condition inside the Sum(Units) should cause including only those "dimensions" with the high margin, in the ranking.
Hope it works for you.
Oleg

Complicated Top 'x' Dimension
David Bonnet Apr 27, 2012 12:00 PM (in response to David Bonnet)Michael/Oleg  You guys are great, both solutions work! Michael I hadn't even thought of using the TOTAl operator in my expression. Oleg passing the results of an expression to the set analysis is brilliant using the double quotes. Thanks again, I learned a lot with this.