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: 
ivanoski
Contributor III
Contributor III

Maintain rank when selecting Filter

I am trying to mainatin a rank of brands in sales in a straight table, but when one filters by a specific BRAND, the rank changes to 1.

 

I am using :

 

rank(SUM({< YEAR= {2020}>} SALES))

 

How do I maintain the rank when i select a specific BRAND as a filter in a straight table?

 

1 Solution

Accepted Solutions
sunny_talwar

I guess all you really need is to uncheck 'Include zero values' under Add-ons -> Data handling and use this expression

Rank(Sum({<YEAR = {2020}, BRAND>} SALES)) * Avg({<YEAR = {2020}>} 1)

View solution in original post

8 Replies
Lisa_P
Employee
Employee

If you are trying to ignore BRAND selection in this measure, add it inside Set expression.

ank(SUM({< YEAR= {2020}, BRAND=>} SALES))

ivanoski
Contributor III
Contributor III
Author

When i do that all the other brands appear for some reason, despite the filter

jonathandienst
Partner - Champion III
Partner - Champion III

Perhaps something like this:

Rank(Sum({<YEAR = {2020}, BRAND>} SALES)) * Only({<YEAR = {2020}>} 1)

(assumes Brand is a dimension)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ivanoski
Contributor III
Contributor III
Author

Brand is indeed a Dimension, but when i add your formula, everything turns to "-"

sunny_talwar

Can you try this

Rank(Sum({<YEAR = {2020}, BRAND>} SALES)) * Avg({<YEAR = {2020}>} 1)

or this

If(Only({<YEAR = {2020}>} BRAND), Rank(Sum({<YEAR = {2020}, BRAND>} SALES)))

 

ivanoski
Contributor III
Contributor III
Author

Rank(Sum({<YEAR = {2020}, BRAND>} SALES)) * Avg({<YEAR = {2020}>} 1)

 

Shows the correct value but makes other brands show up tih the value "-" underneath it when a specific brand is selected

 

If(Only({<YEAR = {2020}>} BRAND), Rank(Sum({<YEAR = {2020}, BRAND>} SALES)))

 

Shows only "-" in all instances 

 

Im begining to think its some sort of bug, 

 

https://www.naturalsynergies.com/q-tip-8-ranking-the-unrankable/

kinda talks about it but even when i use their soultion, it works in the sence that only the selected brand is shown but the rank is still wrong. 

=avg(aggr(rank( sum( {$<BRAND=>} SALES) ), BRAND))

 

works in showing only the selecte dvalue, but rank is still wrong. 

sunny_talwar

I guess all you really need is to uncheck 'Include zero values' under Add-ons -> Data handling and use this expression

Rank(Sum({<YEAR = {2020}, BRAND>} SALES)) * Avg({<YEAR = {2020}>} 1)
ivanoski
Contributor III
Contributor III
Author

**bleep**, you are a genious, 'Include zero values' under Add-ons -> Data handling and use this expression

 

that solved it!!!! ive been 2 days trying to figure this out thanks!!!!!!