Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

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

Hello,

I have a table as shown in the image below

123.png

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

20 Replies
fvelascog72
Partner - Specialist
Partner - Specialist

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.

fvelascog72
Partner - Specialist
Partner - Specialist

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()

))

YoussefBelloum
Champion
Champion

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 ?

mrthomasshelby
Creator III
Creator III
Author

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!

fvelascog72
Partner - Specialist
Partner - Specialist

You´re welcome

sunny_talwar

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

YoussefBelloum
Champion
Champion

Merci Sunny

sunny_talwar

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()


))


Capture.PNG

youssef‌ - Apologize for the delay

YoussefBelloum
Champion
Champion

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 ?

sunny_talwar

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...)