Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Prateek2011
Contributor II
Contributor II

Color Coding Logic for Pivot table

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%}

 
 
 
 
 

image.png

 

Labels (1)
5 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

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.

Prateek2011
Contributor II
Contributor II
Author

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

jaibau1993
Partner - Creator III
Partner - Creator III

Hi

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.

Prateek2011
Contributor II
Contributor II
Author

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

)

 
 
Brett_Bleess
Former Employee
Former Employee

Try the following Help link:

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.