3 Replies Latest reply: May 30, 2010 11:09 PM by Nick Gan RSS

    How to calculate Linear Gauge colour in Pivot Table

    guycuthbert

      I have a QlikView model which 'scores' certain data on a -1 to +1 scale (interpreted as +ve or -ve % value). I have a score for each row in my base data, and then can roll this up by various dimensions through Avg(Score). I can successfully present the score in a Pivot Table - against chosen dimensions - and the values shown are as I would expect.

      I want to present each value as a bar (drawn using a Linear Gauge) and set the size of the bar according to any value I choose - this works fine. I then want to colour each bar by the 'score' - the same value which I know I can present correctly as text.

      I am trying to set the colour as a colour blend between red and green, using the Colormix2() function; this seems to work, but I end up with all bars, in all cells, showing the same colour:

      error loading image

      I calculate the colour for the Linear Gauge as:

      colormix2(aggr(avg(Score), X, [YZ]), lightred(), lightgreen())

      ... but this, clearly, is wrong.

      How do I get the colour to reflect the value of avg(Score) in every cell independent of the others?