Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Conditional Formatting using Colormix1 function

Case :

I have a pivot table in which all the columns are different expressions.

I need to conditionally format each column with the background color from light to dark shade of blue.

Two dimensions are used - Product & Tactic

pivot table.PNG

In the background color of each column expression i have used the below mentioned formula :

colormix1(<EXP>/ max(total aggr(<EXP>, Product )), rgb(180,212,218), rgb(28,95,158))

where <EXP> is the expression.

So the max value in that particular column should be dark blue and min value should be light blue.

If in aggr function i use product and tactic both, still the problem mentioned below is occurring!


Problem :

When i expand any Product , i can see few cells which are not color formatted apart from null values. Please find the image below.

pivot table 2.PNG

Can anyone please help me out with this ?

Thank you in advance!

Regards,

Seerat

1 Reply
terezagr
Contributor III

Re: Conditional Formatting using Colormix1 function

If I understood correctly, you are wondering why your figure in column NPP Contacted for row Tecfidera is having a white background?

I think that the problem is that your max(<EXP>) (divisor) is for mentioned above equal to 0. In that case, whatever your <EXP> will be, the result will be always evaluated like an error as you cannot divide by 0 and therefore Qlik Sense will apply default colour - White.

The case where you see 0 and you have a colour like a background is when your <EXP> (dividend) is equal to 0. The whole expression will be evaluated like a 0 as this is a valid expression…0/300 is 0.

Screen Shot 2016-05-09 at 19.44.34.png