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: 
RSvebeck
Specialist
Specialist

Get max cell value of all rows and columns

Hi

I have a set of calculated numbers in a table:

Region A    B    C    D
Alfa   1.43 1.23 1.78 1.90
Beta   1.68 2.24 1.38 1.91
Gamma  2.71 2.08 2.02 1.06
Delta  1.68 1.59 1.42 1.15

I want to color the highest number (2.71) red using a expression in the "Background Color" attribute.


I tried this formula:

=IF(rank(rangemax(,,,))=1,rgb(250,200,200))

Where referens to the expression for column A.

But it does not work.

=IF(max(rangemax(,,,))=,rgb(250,200,200))

does not work either...

There is nothing beeing colored...

Any ideas?

I was expecting this to be solved easy, so I must be missing something fundamental here....

Regards

Robert S

Svebeck Consulting AB
1 Solution

Accepted Solutions
swuehl
MVP
MVP

You are using different expression, but you want to get the max value over all expressions?

Not sure if I missed a better approach, but I think you need to use advanced aggregation together with maxrange, but use the full expression inside instead of the labels:

Like

=if("A" = max(total aggr(rangemax( avg({<department={a}>}score), avg({<department={b}>}score)), relation)),red(80) )

in my attached sample.

Hope this helps,

Stefan

View solution in original post

4 Replies
Miguel_Angel_Baeyens

Hi Robert,

In each expression use instead:

If(Max(TOTAL A) = , RGB(250, 200, 200))

And so on.

Hope that helps.

Miguel

swuehl
MVP
MVP

You are using different expression, but you want to get the max value over all expressions?

Not sure if I missed a better approach, but I think you need to use advanced aggregation together with maxrange, but use the full expression inside instead of the labels:

Like

=if("A" = max(total aggr(rangemax( avg({<department={a}>}score), avg({<department={b}>}score)), relation)),red(80) )

in my attached sample.

Hope this helps,

Stefan

RSvebeck
Specialist
Specialist
Author

Hi Miguel.  That expression will give me the max of each column, but what I want is the "single max" of all rows and columns - > The max of all dimensions and expressions. It seems like Stefan has provided a soluition for this. Thanks for taking time to look into this. Reg. Robert

Svebeck Consulting AB
RSvebeck
Specialist
Specialist
Author

Stefan, your solution is good and it solves my issue.

Thanks for taking time to look into this. Best Regards, Robert

Svebeck Consulting AB