Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead 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
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
alexandros17
Partner - Champion III
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

alexandros17
Partner - Champion III
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

alexandros17
Partner - Champion III
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

Gysbert_Wassenaar

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