Qlik Sense background color expression - Conditional Formatting using multiple expressions

[charan_j]

Hi,

I am working on color-coding the background cells by considering all the available expressions in the table.

Eg:

Here the North region has high sales and the West region has medium sales and the East region has low values.

So single color-coding expression should be applied across all three expressions.

Can we achieve the same theme in Qlik Sense table?

[vishalarote]

Hi Charan_j,

Create 3 colors variables
vColorDark: rgb(10, 163, 12)
VColorLight: rgb(214, 17, 17)
vColorMiddle: rgb(215, 222, 22)

Then used below function in background color expression:

ColorMix2((rank(total COlumn(1))/(noofrows(TOTAL)/2))-1,$(vColorDark), $(VColorLight),$(vColorMiddle))

[charan_j]

Hi @vishalarote,

Thanks for the reply !!

The way you suggest gives me the below output.

But the output needed is different as shown below which combines all the values from all 3 expression columns and then applies the unique color formatting.

[vishalarote]

Hi Charan_j,

Try this Hope so you will get perfect result

Variables:

vLightRed: rgb(219, 77, 86)
vDarkRed: rgb(232, 19, 33)
vLightYellow: rgb(230, 237, 100)
vDarkYellow: rgb(219, 230, 14)
vLightGreen: rgb(93, 245, 111)
vDarkGreen: rgb(9, 150, 26)

Then used below function in background color expression:

For East:

if(sum(East)>0 and sum(East)<=400,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightRed),$(vDarkRed)),
if(sum(East)>400 and sum(East)<=800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightYellow),$(vDarkYellow)),
if(sum(East)>800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightGreen),$(vDarkGreen))
)))

For West:

if(sum(West)>0 and sum(West)<=400,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightRed),$(vDarkRed)),
if(sum(West)>400 and sum(West)<=800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightYellow),$(vDarkYellow)),
if(sum(West)>800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightGreen),$(vDarkGreen))
)))

For North:

if(sum(North)>0 and sum(North)<=400,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightRed),$(vDarkRed)),
if(sum(North)>400 and sum(North)<=800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightYellow),$(vDarkYellow)),
if(sum(North)>800,
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightGreen),$(vDarkGreen))
)))

[charan_j]

Hi Vishalarote,

But the problem in real-time data is values are changing dynamically. I am unable to set a range of values to color it.

For example, the range may vary like 0-1000, 1000-2000, >2000 in random order. So the gradient should be changed respectively.

[vishalarote]

Then Do one thing divide total value by 3 (i.e. Three color division) and make variable as below so you get mid values between v1 to v2 and your condition also vary dynamically.

v1=sum(East)/3
v2=(sum(East)/3)+(sum(East)/3)

if(sum(East)>0 and sum(East)<=$(v1),
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightRed),$(vDarkRed)),
if(sum(East)>$(v1) and sum(East)<=$(v2),
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightYellow),$(vDarkYellow)),
if(sum(East)>$(v2),
colormix1(rank(total Column(1))/NoOfRows(total),$(vLightGreen),$(vDarkGreen))
)))