Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to apply conditional colour formatting in my (pivot)table for a calculated measure/attribute.
I understood you can do this in "Background Color Expression" under the attribute measure? Though my condition is not straightforward as well as it is for a calculated attribute. (see attachment for the explanation) How to set up the expression? And how to create faded colors?
It is working now, I will explain the steps done to reach the solution:
Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)
Aggr(Sum(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
Aggr(Avg(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
Aggr(nodistinct
Aggr(Avg(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
, FCO
)
=if($(Cell)>$(Total),
ColorMix1(RangeMin($(Ratio) - 1,1), Argb(255,255,230,230), Argb(255,255,26,26)),
If($(Cell)<$(Total),
ColorMix1(-($(Ratio) - 1), Argb(255,230,255,230), Argb(255,26,255,26)),
White()))
The Glitch is with the Totals row, it should have white background, I blame this to rounding, I did not try to figure out how to round it properly; the figures in the Totals row are generated by Qlik, our total figure was generated with Aggregation and unfortunately they did not match 😞
Attached is the new version of the QVF file.
This is the application QVF file
Use the Colormix1 function. You will need to test for red, green, and blue scenarios. For the red scenario, Colormix1(Value,White(),Red()) will produce shades of reds.
Thanks, but what should be the expression? Since I need to compare it against the total average of the workorder
Hopefully, the information at these links will help:
https://community.qlik.com/t5/New-to-QlikView/Explain-Colormix1-ARGB/td-p/773801
http://qlikshow.com/information-design-tips-on-qlik-sense/
https://data-flair.training/blogs/qlik-sense-color-functions/
If(Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID]) > Sum(Total [Sum of Total Sum of Hours[ITM]]])/Sum(Total [Count of EQ ID]),
Colormix1((Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID])/Max(Aggr(Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID]),[Sum of Total Sum of Hours[ITM]]]))),White(),Blue()),
If(Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID]) < Sum(Total [Sum of Total Sum of Hours[ITM]]])/Sum(Total [Count of EQ ID]),
Colormix1((Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID])/Max(Aggr(Sum([Sum of Total Sum of Hours[ITM]]])/Sum([Count of EQ ID]),[Sum of Total Sum of Hours[ITM]]]))),White(),Green()),
White()))
Hi @Kirsten
I found your problem challenging, so I played with it, managing to color the table's cells based on the conditions you explained, I attached the solution QVF for you to review; a summary of my steps are:
=if(W2>Round(Avg(TOTAL W2),0.01),
ColorMix1(RangeMin(W2/Round(Avg(total W2),0.01)-1,1), Argb(255,255,230,230), Argb(255,255,26,26)),
If(W2<Round(Avg(TOTAL W2), 0.01),
ColorMix1(-(W2/Round(Avg(total W2),0.01)-1), Argb(255,230,255,230), Argb(255,26,255,26)),
White()))
Attached is my sample project.
Hope this helps,
Oh wow @ArnadoSandoval thanks! It looks impressive. Thank you for that. I looked at the qvf and the code. My data structure is slightly different with a calculated measure in it. The calculated measure is the same as Avg in your code, this calculated measure is: Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID]). When I add this calculated measure in the code it gives an error. FCO in my code is the same as W (W for workorder is more clear, I changed it to W to clarify) . QVF is added for clarification -> see attachment
=if(FCO>Round(Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])(TOTAL FCO),0.01),
ColorMix1(RangeMin(FCO/Round(Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])(total W2),0.01)-1,1), Argb(255,255,230,230), Argb(255,255,26,26)),
If(FCO<Round(Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])(TOTAL FCO), 0.01),
ColorMix1(-(FCO/Round(Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])(total W2),0.01)-1), Argb(255,230,255,230), Argb(255,26,255,26)),
White()))
@ArnadoSandoval what do I need to change in the code in the message above to have it working? It is currently not fully working it returns an error when I add the calculated metric: Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])
I do not know yet, it is a pivot table, interesting challenge !!!
Regards,
It is working now, I will explain the steps done to reach the solution:
Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)
Aggr(Sum(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
Aggr(Avg(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
Aggr(nodistinct
Aggr(Avg(Aggr((Sum([Sum of Total Sum of Hours [ITM]]])/Sum([Count of EQ ID])), Market, FCO)), FCO)
, FCO
)
=if($(Cell)>$(Total),
ColorMix1(RangeMin($(Ratio) - 1,1), Argb(255,255,230,230), Argb(255,255,26,26)),
If($(Cell)<$(Total),
ColorMix1(-($(Ratio) - 1), Argb(255,230,255,230), Argb(255,26,255,26)),
White()))
The Glitch is with the Totals row, it should have white background, I blame this to rounding, I did not try to figure out how to round it properly; the figures in the Totals row are generated by Qlik, our total figure was generated with Aggregation and unfortunately they did not match 😞
Attached is the new version of the QVF file.