Pivot Chart - Red,Amber Green Rating of Value

[#1]

Hi, 

Im currently using the following expression to shade the 'value' cell of a pivot chart Red for below tolerance and green for within tolerance:

=if(not isnull(tolerance),if(If(single_value='N',(sum(val1)/sum(val2)*100), sum(val3)) <tolerance,RGB(255,0,0),RGB(0,255,0)))

What I need to add is an Amber shade.  This is where the value calculation is within 90-99% of the 'tolerance'  value.  Red would then need to be below 90% of the tolerance value.  Is this possible to do with my current expression?

Many thanks,

Fiorano

[alexandros17]

That's it

=

if(not isnull(tolerance),
if(If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) <tolerance,
RGB(255,0,0),
if(If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) >=tolerance and If(single_value='N',(sum(val1)/sum(val2)*100),sum(val3)) <=99,
'Amber',
RGB(0,255,0)

)

)

)

Hope it helps
 

[#3]

Hi, thanks for your reply.  For some reason it has only worked for 1 row in my pivot (where the tolerance value is 85%). All of the other tolerance values are set at 100%.... Any ideas?

Many thanks again,

Fiorano

[alexandros17]

Could you provide an example?

[#5]

Hi,

Ive attached an xls copy.  If you look at 'Item 13' (row 15) there are values that fall between the 90%-99% range of the 100% tolerance which should be 'amber' but they are kept at red.

Another one is 'Item 5'

Hope this helps,

Many thanks again,

Fiorano

[alexandros17]

I'm sorry but I can't answer without looking at the document ...

[#7]

Hi Sorry for the late reply.

I've attached a sampel file where I am formatting the cell to green or red.

As explained in previous posts.  If the calculated value is :

• <90% of the Tolerance - Red Background with White Text
• >=90% and <99% of the Tolerance - Amber Background and Black Text
• >=99% of the Tolerance - Green Background and Black Text

Ive added a 'Rating LookUp' Table to try and simplify the expression - not sure if this helps in any way???

Really appreciate your time in this!

Fiorano

[Gysbert_Wassenaar · Accepted solution]

See attached qvw.

[#9]

Awesome !!  This is perfect!  Many thanks for your help.

Fiorano

[#10]

Hi,

Thanks again for your previous answer.  I've noticed that some questions are measured by being over target.  For Example:

Pseudo Code

If Question_number in (1,2,3,4) then red or amber if value is less than Target and Green if value is above Target

If Question_number in (5,6,7,8,9,10) then red or amber if value is above Target and Green if value is less than Target.

Are you able to modify your previous solution?

Many thanks,

Fiorano