3 Replies Latest reply: Apr 27, 2012 12:00 PM by David Bonnet RSS

Complicated Top 'x' Dimension

David Bonnet
In my pivot tables I frequently use an expression like below in one of my dimensions to return the top 'x':
=aggr(if(rank(sum(Units),4,1) < 11,Dimension1),Dimension1)
This is pretty straight forward, after I select "Do not show null values" it will only return dimensions that aggregated have the top 10 Units.
My question is how would I answer ...
"I need the top 10 based on Units BUT their Gross Margin must be over 1000"?  I've been trying all sorts of things and it won't work.  This will return only the one or two that qualify for top 10 Units and have a Gross Margin over 1000.
=aggr(if(rank(sum(Units),4,1) < 11 AND sum([Gross Margin]) > 1000,Dimension1),Dimension1)
I even tried embedding another if within an if and it doesn't work:
=aggr(if(rank(sum(if(sum([Gross Margin]) > 1000,Units)),4,1) < 11,Dimension1),Dimension1)
Could set analysis play a role here, possible the P() function?  Any suggestions would be appreciated.  Thanks!
  • Re: Complicated Top 'x' Dimension
    Michael Steedle

    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.

  • Complicated Top 'x' Dimension
    Oleg Troyansky

    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

    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.