topic Re: Highlight Min, Max and median numbres in table/pivot with colors in QlikView

Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Sat, 16 Nov 2024 00:04:56 GMT

Hi All,

Is it possible to highlight Min, Max and Median value with different colours in a table/ Pivot?

Request you to please check and advice. screenshot attached for your reference

my measure is count([CTMP CPTY Reason])

Appreciate your assistance.

Thanks,

Richard

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Tue, 25 Aug 2020 15:23:12 GMT

You can use below expression in background color of your measure. Where Dimension1,Dimension2 are pivot table dimensions. You can fill the RGB color values based on requirement

=if( count([CTMP CPTY Reason]) = max(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,),

if( count([CTMP CPTY Reason]) = min(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,),

if( count([CTMP CPTY Reason]) = median(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,))))

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Tue, 25 Aug 2020 15:35:11 GMT

Hi Kush,

Thanks for your quick support.

I tried as per your suggestion, not sure why only #2 are getting highlighted. I used expression as below -

=if(count([CTMP CPTY Reasons]) = max(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), red(),

if(count([CTMP CPTY Reasons]) = min(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), green(),

if(count([CTMP CPTY Reasons]) = median(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), blue ())))

Thanks in advance , please advice if I am missing something above. screenshot attached.

Regards,

Richard

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Tue, 25 Aug 2020 16:08:55 GMT

looks like you have 3 dimensions in pivot table so in aggr you need to include all the dimensions

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Tue, 25 Aug 2020 16:22:14 GMT

No I just have 2 dimensions -

[CTMP Joint Touch Statement],

[CTMP Market Country] and one measure

count([CTMP CPTY Reasons]

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Tue, 25 Aug 2020 16:35:00 GMT

what is Touched Trades?

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Tue, 25 Aug 2020 16:39:12 GMT

That is nothing but CTMP joint touch statement dimension which I have renamed to touched trades 🙂

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Tue, 25 Aug 2020 16:58:23 GMT

What is capacity indicator, coupon rate amount then?

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Tue, 25 Aug 2020 17:02:38 GMT

capacity indicator, coupon rate amount, etc are the reasons listed under CTMP CPTY reasons which we are counting under measure against 2 dimensions

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Tue, 25 Aug 2020 17:11:51 GMT

So you need to include CTMP CPTY reasons as well in aggr

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Wed, 26 Aug 2020 01:52:17 GMT

Thank you Kush, it worked fine now. Appreciate your time and support 🙂

Re: Highlight Min, Max and median numbres in table/pivot with colors

richard24best — Wed, 26 Aug 2020 01:53:06 GMT

Thank you Kush, it works fine now. Appreciate your time and support.

Re: Highlight Min, Max and median numbres in table/pivot with colors

Kushal_Chawda — Wed, 26 Aug 2020 09:23:40 GMT

But I would advise that you should mark the correct answer to the reply in which I had suggested if statement. because it's the correct one .