1 Reply Latest reply: May 9, 2016 2:46 PM by Tereza Gregorova RSS

    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!




        • Re: Conditional Formatting using Colormix1 function
          Tereza Gregorova

          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