topic Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions in QlikView

Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

mrthomasshelby — Wed, 28 Feb 2018 08:08:22 GMT

Hello,

I have a table as shown in the image below

This table has the option of choosing multiple dimensions from a multi box named 'Columns/X-axis'. Now what I want is to color the minimum and maximum values in each row of the table. For example, say in the first row(Malaysia,Peninsular,State1) Supplier A has the least value(273) and Supplier E has max value(325). Say I'd like to color the least value(273) as green and max value(325) as red. I want to do this for all the rows in the table. Any help is much appreciated. I have tried the following expression in the Background option for the 'AVG' expression but it's not working:

if(Avg($(v_MP_Rate_Type))=Max(TOTAL <[Supplier Name]> $(v_MP_Rate_Type)),RGB(255,0,0))

I have attached the app for your reference. TIA! stalwar1‌

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 09:16:36 GMT

Hi,

Try with this in background color of the expression:

If(AVG = Min(TOTAL <$(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), Red(),

If(AVG = Max(TOTAL <$(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), blue()

))

Saludos

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Wed, 28 Feb 2018 09:33:15 GMT

Hi fvelascog72‌,

I tried to do it with nested aggr, do you have an idea why it doesn't work like this ?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 09:49:12 GMT

Hi,

You mean nested aggr instead nested if?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Wed, 28 Feb 2018 09:49:48 GMT

Yes

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

mrthomasshelby — Wed, 28 Feb 2018 09:55:05 GMT

That works! Thanks a lot Federico! Can you explain briefly the rationale behind this expressions if possible? Thanks again!

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 09:55:57 GMT

Could you write here what are you trying?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 09:59:07 GMT

With the aggr you select all values and with the TOTAL <$(v_MP_Dim_3)> you choose row by row. More or less?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Wed, 28 Feb 2018 10:01:31 GMT

EDITED

First, i was trying this:

if(AVG = Min(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3)))...

but on the table I have only one max value and one min value of all the lines.

and you added another aggregation on the dimension 3, with total <dimension 3>, I want to know why ?

and so I tried this, but it is not working:

if(AVG = aggr(Min(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))),$(v_MP_Dim_3)), Red())

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

mrthomasshelby — Wed, 28 Feb 2018 10:10:28 GMT

Hello Federico, I'm getting the result as shown in the image. As you can see, the coloring is happening for some rows but not for some other rows. For example you can see in Region 'East','State 3' only the Max value is colored while the Min value is not. Similarly, in Region 'Peninsular', only Min is colored and not max. This is happening because State 3 is present in both the regions I guess. Is there anyway we can modify this expression to get the two colors for each row. For example, in Region 'Peninsular', 'State 3' Supplier A should be colored in Red while Supplier E should be colored in blue? Thanks again!

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 10:11:45 GMT

youssef belloum escribió:

but on the table I have only one max value and one min value of all the lines.

That´s why you must choose row by row with total <dimension 3>.

I think doing this in pivot tables is risky because it will not work if you pivot the dimensions.

If you search other option tell me. Always learnig.

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 10:15:12 GMT

Try with:

If(AVG = Min(TOTAL <$(v_MP_Dim_2), $(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), Red(),

If(AVG = Max(TOTAL <$(v_MP_Dim_2), $(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), blue()

))

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Wed, 28 Feb 2018 10:18:14 GMT

Thank you,

I am not yet very comfortable with this kind of problematics..

It would be very interesting to have the advice of one of the experts here, like stalwar1‌, what would you have used here Sunny ?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

mrthomasshelby — Wed, 28 Feb 2018 11:44:44 GMT

This is perfect! I understand now that we're considering the cells based on vDim2 and vDim3. Thanks a lot for all the help again Federico! Cheers!

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

fvelascog72 — Wed, 28 Feb 2018 11:48:16 GMT

You´re welcome

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

sunny_talwar — Thu, 01 Mar 2018 11:30:35 GMT

I will try to get back to you on this sometime today

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Thu, 01 Mar 2018 12:46:30 GMT

Merci Sunny

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

sunny_talwar — Wed, 14 Mar 2018 12:34:08 GMT

I think you might have been trying to do this.... right?

If(AVG = Aggr(NODISTINCT Min(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), $(v_MP_Dim_2), $(v_MP_Dim_3)), Red(),

If(AVG = Aggr(NODISTINCT Max(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), $(v_MP_Dim_2), $(v_MP_Dim_3)), blue()

))

youssef‌ - Apologize for the delay

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

YoussefBelloum — Wed, 14 Mar 2018 12:55:49 GMT

Yes, thank you so much.. I was trying with Aggr only and it didn't work..

it is perfect

You know me I love the details, so I'll ask a question here too:

between the solution of federico: Min(TOTAL <$(v_MP_Dim_3)> ..aggr(avg

and yours: Aggr(NODISTINCT Min(Aggr(avg

both are the same ? in terms of performance maybe ?

Re: Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

sunny_talwar — Wed, 14 Mar 2018 16:20:38 GMT

I like using TOTAL <> instead of Aggr(NODISTINCT and more or less they give the same output.... but more recently I have come across some examples where TOTAL <> doesn't work and I have had to fall back to Aggr(NODISTINCT...).

So, my first attempt is usually to get it work using TOTAL <>... if that doesn't work, I fall back to Aggr(NODISTINCT...)