How to calculate Linear Gauge colour in Pivot Table

[#1]

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:

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?

[#2]

Anyone from QlikTech have any ideas on this one?

[#3]

Hello,

Is it what you want to achieve ?

Philippe

[#4]

Hi Philippe,

I'm trying to do the same, but my calculation is in percentage.

Could you show how do i set the following in linear gauge?

-1% to 30% = red
31 to 70% = yellow
71% above = green

thanks