Conditional Formatting a Column based on its Total

[NishantPatel]

Hi,

I have a table here in QlikSense which requires conditional formatting on Column 3.

Column 3 is calculated by (Column 1/Column 2) * 100 = % value

For example, (492/632) * 100 = 77.85%

Based on the total of column 3 (i.e. 102,361.46/10,091,561.00 * 100 = 1.01%), I need to apply conditional formatting to this column for each row.

Rule: If the row value of column 3 is greater than or equal to the total of column 3 (1.01%) the background colour should be green and if its less than the total of column 3 (1.01%) the background colour should be red. Below is an example screenshot of what I am trying to achieve.

Thank you

[sasikanth · Accepted solution]

HI, 

use column1 & column2 original expressions 

ex: If ( ( (Column 1/Column 2) * 100) >=  (sum( TOTAL Column1 exp)/ sum(TOTAL Column2 exp) *100), GREEN(), RED())

You can also create a variable to store Column3 total value and use it background color expression

LET vCol3total = (sum( TOTAL Column1 exp)/ sum(TOTAL Column2 exp) *100);

BG color exp: If ( ( (Column 1/Column 2) * 100) >=  $(vCol3total) , GREEN(), RED())

Thanks, 

Sasi

[NishantPatel · Accepted solution]

Hi Sasi,

Thank you for your solution.

I was able to get this working but needed to make a slight modification to your original expression by adding in "sum" to column 1 and column 2 expressions (see in bold below).

=if(((sum(Column 1 exp)/sum(Column 2 exp)) * 100) >=  ((sum(TOTAL Column1 exp)/sum(TOTAL Column2 exp)) * 100), RGB(0,255,0), RGB(255,0,0))