Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tduarte
Partner - Creator II
Partner - Creator II

How to Rank and ignore selections?

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:

Capture.PNG.png

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?

1 Solution

Accepted Solutions
tduarte
Partner - Creator II
Partner - Creator II
Author

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.

View solution in original post

14 Replies
Not applicable

Telmo,

Try this and adapt it to your model :

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

aggr() is bound to the user state. Does not accept (directly) a set analysis

sum() accepts that set analysis

Fabrice

tduarte
Partner - Creator II
Partner - Creator II
Author

Hi Fabrice,

Are you sure Rank can be done that way? Where does the Product fit in the function parameters?

rank( [ total ] expression [ , mode [, format ] ] )

Not applicable

Yes, I am sure

Rank(expression, dimension)

where:

Expression = sum({set analysis} measure)

Dimension = product

Please, try it but adapt it to your model

Fabrice

tduarte
Partner - Creator II
Partner - Creator II
Author

Ok, so I gave it a try and it kind of works but only if I have only one Vendor selected which is not an option. When there's more than one Vendor then it does not rank correctly.

My aggregation/rank needs be based on these 5 dimensions:

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


Any thoughts?

tduarte
Partner - Creator II
Partner - Creator II
Author

In case this can't be done with Rank(), are there any other options to get the same result?

Not applicable

Please try

AGGR(RANK(sum({1}[Net Sales])),[Branch Name],[Sales Operator])

Not applicable

For several dimensions, I used this syntax and gave me the appropriate result (if I have understood your requirements):

 

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

Fabrice

Not applicable

Capture.PNG.png

Not applicable

something like below not working ?

aggr(Rank(sum({<Product>}vol)),Product)