topic Re: Color Coding Logic for Pivot table in QlikView

Color Coding Logic for Pivot table

Prateek2011 — Sat, 16 Nov 2024 19:28:32 GMT

hi,

Please help me to apply the Red, Amber ,Green Color Logic for the Percentage column.

Red : if Percentage Column data < Overall Total { 117%}

Amber: if Percentage Column data >= Overall Total {117% } and

Percentage Column data < [( max( subTotal column Col1) {126.5%} ]

Green: if Percentage Column data > = ( max( subTotal column Col1) {126.5%}

Re: Color Coding Logic for Pivot table

jaibau1993 — Thu, 12 Dec 2019 08:03:33 GMT

Hi!

Try something like

If( Column(1) < Sum(TOTAL Percentaje), red, If( Column(1) >= Sum(TOTAL Percentaje) and Column(1) < Sum(TOTAL <Col1>, amber, If( Column(1) >= Sum(TOTAL <Col1>, green)))

Note that I provided the logic. You'll have to accomodate to your actual fields and functions (share a sample of data so I can give you a more accurate answer/explanation).

Regards,

Jaime.

Re: Color Coding Logic for Pivot table

Prateek2011 — Thu, 12 Dec 2019 09:17:39 GMT

hi,

thank's , but the logic is not working for the colors .

If( Percentage < Sum(TOTAL Percentage), red,
If( Percentage >= Sum(TOTAL Percentage) and Sum(Percentage) < Sum(TOTAL <Col1>, amber,
If( Percentage >= Sum(TOTAL <Col1>, green)))

Re: Color Coding Logic for Pivot table

jaibau1993 — Thu, 12 Dec 2019 12:01:48 GMT

If you used the expression

If( Percentage < Sum(TOTAL Percentage), red, If( Percentage >= Sum(TOTAL Percentage) and Sum(Percentage) < Sum(TOTAL <Col1>, amber, If( Percentage >= Sum(TOTAL <Col1>, green)))

It won't work because it is still a logic template:

What expression are you using in your "Percentage" expression? You should use it instead of Sum(Total Percentage)
What dimension are you using as "Col1"?
Use proper color functions like red(), green(), yellow(), RGB(255,0,0)...

Regards,

Jaime.

Re: Color Coding Logic for Pivot table

Prateek2011 — Wed, 18 Dec 2019 08:46:11 GMT

Hi,

If Anyone Has Different Approach . Please share the Knowledge.

I Used 1 Logic for color .

In which i use two Variable : One for TOTAL and Another One For Max( Sub Total) and Used in Expression Background Color and Text Format Chart.

For Example :

Percentage = Sum(A)/Sum(B)

vPercentageTotal = Sum(Total A)/Sum(Total B) = 117%

vPercentageSubTotalMax = Max(Aggr(Sum(A)/Sum(B),Col1)) = 126.5%

Expression BackGround :

If( Sum(A)/Sum(B) < vPercentageTotal , Red(),

Sum(A)/Sum(B) >= vPercentageSubTotalMax , Green(),

Yellow()

)

Expression Text Color:

If( Sum(A)/Sum(B) < vPercentageTotal , White(),

Sum(A)/Sum(B) >= vPercentageSubTotalMax , White(),

Black()

)

Re: Color Coding Logic for Pivot table

Brett_Bleess — Wed, 18 Dec 2019 19:33:34 GMT

Try the following Help link:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Visual%20Cues.htm

This would use the Visual Cues tab in the Pivot Table settings, but I am assuming Percentage is an expression, not a dimension, if it is a dimension, they you have to use the background setting per the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Colors-in-charts/ba-p/1475280

As far as the expression, you will likely need to attache sample app, so folks can see your data model and chart etc., so they can look everything over to properly determine the correct expression to solve things, if you cannot attach that, I am not sure you will get further answers given the other poster tried without it and could not help you get it.

My post will kick things back up though, so maybe someone else will have a look and take another try at things.

Regards,
Brett