How can we get multiple colour heat map with input box values (esp in Pivot tables)

[#1]

Hi all,

Can you please help me to create Pivot table (Heat Map) with input box values.

Check below Pivot table:

Pivot table
	Year	2009
	Month	Jan	Feb	Mar	Apr
a		20%	40%	80%	75%
b		30%	30%	50%	50%
c		40%	20%	75%	80%

Input Box:

Input Box
vGreen	=	0.8
vBlue	=	0.5
vRed	=	0

vGreen should be >=0.8
vBlue should be >=0.5 <0.8
vRed should <0.5

Question: I've created three variables (vGreen, vBlue and vRed) and assigned them to input box to change the numbers manually. How can I change colours in Pivot table if numbers entered manually in Input Box.

Thanks for your help.

Regards,

Sriniv

[hectorgarcia]

hello please find attached and example of you are looking for I hope it help

Hector garcia

[hectorgarcia]

OOps i Inverted the colors, change the expression for this one

=

IF(Percentage>=0.8,

argb(

  // Calculate the Alpha for ARGB()

  sum(Percentage) // This cell's SalesAmount amount

  // Divided by the max cell SalesAmount - gives a number between 0 and 1, 1 being max

  /max(all aggr(sum( {1<Flagmejora={'1'}>} Percentage), Month))

// / max(TOTAL aggr(sum(Percentage), Averagefirst1, Averagelast1))

  * $(vGradient) // tiMonth 255 gives the alpha value - the max value gets 255

,0,172,0 // R,G,B - green

) ,

IF(Percentage>=0.5 and Percentage<0.8,

argb(

  // Calculate the Alpha for ARGB()

  sum(Percentage) // This cell's SalesAmount amount

  // Divided by the max cell SalesAmount - gives a number between 0 and 1, 1 being max

  /max(all aggr(sum( {1<Flagmejora={'0'}>} Percentage), Month))

// / max(TOTAL aggr(sum(Percentage), Averagefirst1, Averagelast1))

  * $(vGradient) // tiMonth 255 gives the alpha value - the max value gets 255

,0,128,255 // R,G,B - green

),

IF(Percentage<0.5,

argb(

  // Calculate the Alpha for ARGB()

  sum(Percentage) // This cell's SalesAmount amount

  // Divided by the max cell SalesAmount - gives a number between 0 and 1, 1 being max

  /max(all aggr(sum( {1<Flagmejora={'-1'}>} Percentage), Month))

  /// max(TOTAL aggr(sum(Percentage), Averagefirst1, Averagelast1))

  * $(vGradient) // tiMonth 255 gives the alpha value - the max value gets 255

,255,0,0 // R,G,B - green

)

)))

[hectorgarcia]

corrected version