How to Rank and ignore selections?

[tduarte]

Hi Community,

How to Rank and ignore selections if it's not possible to use set analysis in the Rank function?

I have an issue with the table below:

The Rank only ranks the values that have passed the data confidentiality rules in the IF condition.

Here's the Rank expression:

Aggr(
  Rank(If(Count({<[Reseller Size Band]=>} DISTINCT ResellerID)>=5
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Units),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Units)<= 0.75
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Revenue),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Revenue)<= 0.75
  ,-sum({<[Reseller Size Band]=>} Revenue)),4,1)
,Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

I need the Rank to stay as is even after making a selection in one of the Bands.

I.e.: If I select >3m, it should should still show Rank=3 and not 1.

Any thoughts?

[tduarte]

Thank you all for you replies. Let me try to clarify what I need and what I've got so far.

Like I mentioned before, I need to rank each combination of Country,Category,Vendor,[Reseller Size Band],[MMM-YY].

First I've done a simple Rank:

     Rank(-sum( Revenue),4,1)

So far so good. But next I need the rank to be the same after selecting one Reseller Size Band. This is how it should look like:

So I modified the expression to Rank(-sum({<[Reseller Size Band]=>} Revenue),4,1), but the problem is that it does not filter the table rows, the table stays the same as the one at the top.

Vinay Naran - I can't use {1} because I need to take selections into account and rank each combination

Fabrice Aunez - I have tried this expression but not working (Error in expression - rank function):

Aggr(

  Rank(TOTAL -sum({<[Reseller Size Band]=>} Revenue),Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

,Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

Hope that was clear and thanks again for your help.

[#3]

Try this:

Aggr(

  Rank(sum({<[Reseller Size Band]=>} Revenue),[Reseller Size Band]), [Reseller Size Band])

(see my first expression in this thread)

If I understand, the rank() concerns only the reseller size (from 1 to the number of resellers). If you do a Total and several dimensions, you will get a rank from 1 to a cartesian product = Nb of values of Dim1 x  Nb of values of Dim2 etc.

Fabrice

[tduarte]

Fabrice,

Already tried that but not working either.

This is what I get:

[#5]

I have done that:

aggr(rank(sum({<Customer=>} Amount), Customer), Product, Customer)

In fact you are ranking on 1 dimension. Therefore the dim inside rank is unique: here above is Customer. No use of Total keyword because each time you change the value of another dim, you do another rank.

But your table is composed of several dimensions: so, the aggr() function must get several dimensions (the ones of your table)

See the result I obtain with it.

Fabrice

[tduarte · Accepted solution]

Thanks again Fabrice.

I found the solution but it doesn't make sense in my opinion.

My original expression was conceptuality correct, the problem was in the Aggr dimensions order:

Original:

Aggr(
  Rank(If(Count({<[Reseller Size Band]=>} DISTINCT ResellerID)>=5
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Units),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Units)<= 0.75
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Revenue),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Revenue)<= 0.75
  ,-sum({<[Reseller Size Band]=>} Revenue)),4,1)
,Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

Working:

Aggr(
  Rank(If(Count({<[Reseller Size Band]=>} DISTINCT ResellerID)>=5
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Units),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Units)<= 0.75
  AND Max({<[Reseller Size Band]=>}Aggr(Sum({<[Reseller Size Band]=>}Revenue),Country,Category,Vendor,[Reseller Size Band],[MMM-YY],ResellerID))/Sum({<[Reseller Size Band]=>}Revenue)<= 0.75
  ,-sum({<[Reseller Size Band]=>} Revenue)),4,1)
,[MMM-YY],Country,Category,Vendor,[Reseller Size Band])

Can you see the difference in line 6?

Original: Aggr( ... ,Country,Category,Vendor,[Reseller Size Band],[MMM-YY])

Working: Aggr( ... ,[MMM-YY],Country,Category,Vendor,[Reseller Size Band])

I don't understand why but looks like the order in the Aggr() matters.

I'll leave that explanation for the experts.