Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Not applicable

## Pivot Chart - Red,Amber Green Rating of Value

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

1 Solution

Accepted Solutions

See attached qvw.

talk is cheap, supply exceeds demand
9 Replies
Partner - Champion III

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

Not applicable
Author

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

Partner - Champion III

Could you provide an example?

Not applicable
Author

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

Partner - Champion III

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

Not applicable
Author

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

See attached qvw.

talk is cheap, supply exceeds demand
Not applicable
Author

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

Fiorano

Not applicable
Author

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

Community Browser