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