Rank / Max with Set Analysis to Highlight best/ worst in column?

[110]

I've been asked why there's not an option like excel for conditional formatting columns, rows etc. and I don't have a good answer when it comes to set analysis.

Because Rank () won't work where set analysis is used, I'm stumped.


Assuming the below is my data-set

Name	Food	Week	Amount
Bob	Fish	1	2
Bob	Cheese	1	3
Bob	Bread	1	4
Bert	Fish	1	5
Bert	Cheese	1	1
Bert	Bread	1	8
Bob	Fish	53	9
Bob	Cheese	53	2
Bob	Bread	53	8
Bert	Fish	53	7
Bert	Cheese	53	1
Bert	Bread	53	2

If I added a rule per column  to pick the top 1 values and highlight - I'd get something like this ( created with Excel conditional formatting for top 1 ) 

I'm using set analysis , so to do the same is to compare the aggregate, but it returns true for both rows 

Using 

if(
Sum( {<Food={"Fish"},[Week]={53}>}Amount)=

max(aggr(Sum(total <Name> {<Food={"Fish"},[Week]={53}>}Amount),Name)),
red(),green()
)

Are there plans to introduce something simple into table formatting to allow for simpler solutions to this issue?

Like allowing highlighting of top 1, 10, 20 etc, and gradients etc.  would be simple to do at a hypercube level , but is a dark art trying to figure out with set analysis.

[marksouzacosta · Accepted solution]

Hi @110 ,

Try the following:

Expand your measure, look for Background color expression and add the following Expression:

If(Rank(Sum(Amount))=1,Green())

For Set Analysis, you can do something like this:

If(Rank(Sum({<Food = {'Fish'}>} Amount))=1,Green())

[110]

Thanks @marksouzacosta   - that did it,  I'm sure I was initially using a format similar to that, trying to keep it simple, yet I was getting unexpected behaviours - it have have been where I was using a slightly different  measure in the rank function, and going down a rabbit hole of trying to match with max/aggr. 

On the plus side, hopefully those searching for Rank() with Set analysis will find a newer post than I did, google seems to find posts exclusively from 2011 or earlier relating to QlikView. 🙂

I can confirm this works in 2024 with Qlik SaaS..