+ Start a Discussion
Sienna Luard 3Sienna Luard 3 

customer health score formula

I have created the following cutomer health formula field that works perfectly.

IF( ISPICKVAL( Successful_Health_Check__c ,"No" ) ,0,10 )+
IF( ISPICKVAL( Usage_at_Expected_level__c ,"No" ) ,0,25 ) + 
IF( ISPICKVAL( All_Licenses_Paid_For__c  ,"No" ) ,0,10 ) + 
IF( ISPICKVAL( NPS_Greater_Than_6__c ,"No" ) ,0,20 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"3 Months" ) ,0,0 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"6 Months" ) ,0,10 ) + 
IF( ISPICKVAL( Last_In_Person_Meeting_Time_Frame__c ,"12 Months" ) ,0,30 ) + 
IF( ISPICKVAL( Change_in_Management__c ,"Yes" ) ,0,5 ) +
IF( ISPICKVAL( Overdue_30_Days__c ,"Yes" ) ,0,20 ) +
IF( ISPICKVAL( Overdue_60_Days__c  ,"Yes" ) ,0,35 ) +
IF( ISPICKVAL( Level_2_Support_Tickets__c  ,"Yes" ) ,0,55 ) +
IF( ISPICKVAL( Identified_Red_Flag_Issue__c  ,"Yes" ) ,0,55 )

We are categorizing customers into 3 categories based on the score that is calcuated from this formula.

Green = 0 - 30 
Yellow = 31 - 51 
Red 51- 9999

I would like to create a formula field that indicates their status and gives a visual indicator.  I have the following formula in an opporuntiy field that works well in categorizing Opps by Stage.

IMAGE(
CASE(StageName , 
"Negotiating Price & Implementation", "/img/samples/light_green.gif",
"Qualifying", "/img/samples/light_yellow.gif",
"Closed Lost", "/img/samples/light_red.gif",
"/s.gif"),
"status color")

I am having a tough time modifying that formula to show Red, Yellow or Green based on the following scoring tiers:

Green = 0 - 30 
Yellow = 31 - 51 
Red 51- 9999

Any ideas would be most appreciated
Best Answer chosen by Sienna Luard 3
Malni Chandrasekaran 2Malni Chandrasekaran 2
Sienna,
Sorry, Replace AND with && and try.

IMAGE(
CASE(1, 
If(CustomerHealth <= 30, 1, 0  ), "/img/samples/light_green.gif",
If((CustomerHealth > 30 && CustomerHealth <= 51), 1, 0  ),  "/img/samples/light_yellow.gif",
If((CustomerHealth > 51 && CustomerHealth <= 9999), 1, 0  ),  "/img/samples/light_red.gif",
"/s.gif"),
"status color")

All Answers

Malni Chandrasekaran 2Malni Chandrasekaran 2
Sienna,
Please try,
//CustomerHealth field holds the health score.


IMAGE(
CASE(1, 
If(CustomerHealth <= 30, 1, 0  ), "/img/samples/light_green.gif",
If((CustomerHealth > 30 AND CustomerHealth <= 51), 1, 0  ),  "/img/samples/light_yellow.gif",
If((CustomerHealth > 51 AND CustomerHealth <= 9999), 1, 0  ),  "/img/samples/light_red.gif",
"/s.gif"),
"status color")

Hope this helps!
Please mark it as best answer if this helps solving your problem
Sienna Luard 3Sienna Luard 3
Hello Maini, 

I put in your formula:

//CustomerHealth field holds the health score.


IMAGE(
CASE(1, 
If(CustomerHealth <= 30, 1, 0  ), "/img/samples/light_green.gif",
If((CustomerHealth > 30 AND CustomerHealth <= 51), 1, 0  ),  "/img/samples/light_yellow.gif",
If((CustomerHealth > 51 AND CustomerHealth <= 9999), 1, 0  ),  "/img/samples/light_red.gif",
"/s.gif"),
"status color")

I got the following error message:

Error: Syntax error. Missing ')'

Any ideas?
 
Malni Chandrasekaran 2Malni Chandrasekaran 2
Sienna,
Sorry, Replace AND with && and try.

IMAGE(
CASE(1, 
If(CustomerHealth <= 30, 1, 0  ), "/img/samples/light_green.gif",
If((CustomerHealth > 30 && CustomerHealth <= 51), 1, 0  ),  "/img/samples/light_yellow.gif",
If((CustomerHealth > 51 && CustomerHealth <= 9999), 1, 0  ),  "/img/samples/light_red.gif",
"/s.gif"),
"status color")
This was selected as the best answer
Sienna Luard 3Sienna Luard 3
Thanks Maini, I did some tweeking, and got this to work:

IMAGE 

IF 

Customer_Health_Formula__c<=30,"/img/samples/light_green.gif", 

IF 
(Customer_Health_Formula__c > 30 && 
Customer_Health_Formula__c<=51,"/img/samples/light_yellow.gif", 

IF 
(Customer_Health_Formula__c > 51,"/img/samples/light_red.gif", "/s.gif") 


),"/s.gif" 

)
Caroline SmithCaroline Smith
You created very helpful customer health formula of visual indicator which is also beneficial for medical students of consultancy Quality Assignment UK - http://www.qualityassignment.co.uk/.  
 
Kate O'BrienKate O'Brien
Is there a way to layer in non picklist functions in to this? I am trying to attribute a score to number of different field type results to create an over all customer health score and am running in to trouble trying to write the formula to capture the different field types.