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: 
charan_j
Contributor II
Contributor II

Qlik Sense background color expression - Conditional Formatting using multiple expressions

Hi,

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

Eg:

Example.JPG

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?

5 Replies
vishalarote
Partner - Creator II
Partner - Creator II

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
Contributor II
Contributor II
Author

Hi @vishalarote,

Thanks for the reply !!

The way you suggest gives me the below output.

Example 1.JPG

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.

Example 2.JPG

vishalarote
Partner - Creator II
Partner - Creator II

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
Contributor II
Contributor II
Author

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
Partner - Creator II
Partner - Creator II

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