Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hello please find attached and example of you are looking for I hope it help
Hector garcia
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
)
)))
corrected version