topic Re: Get max cell value of all rows and columns in QlikView

Get max cell value of all rows and columns

RSvebeck — Tue, 17 Jan 2012 16:16:37 GMT

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

Re: Get max cell value of all rows and columns

Miguel_Angel_Baeyens — Tue, 17 Jan 2012 16:40:33 GMT

Hi Robert,

In each expression use instead:

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

And so on.

Hope that helps.

Miguel

Re: Get max cell value of all rows and columns

swuehl — Tue, 17 Jan 2012 16:46:59 GMT

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:

=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

Get max cell value of all rows and columns

RSvebeck — Wed, 18 Jan 2012 08:12:24 GMT

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

Re: Get max cell value of all rows and columns

RSvebeck — Wed, 18 Jan 2012 08:19:37 GMT

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

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