function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ 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.
Salman Ali 10Salman Ali 10
It's all b about the complete detail of the best laptops with their specifications. techydiy.com (https://techydiy.com/connecting-raspberry-pi-to-laptop/)
Bigg Boss Ott Live Ott LiveBigg Boss Ott Live Ott Live
Bigg Boss is tv show by colors tv Hosted by Salman Khan each year, This time in Bigg Boss 15
Salman Khan is again making this season much interesting and can make it on the top in TRP.
Bigg Boss Ott Live (https://biggboss15online.net/ott-episodes/)
Bigg Boss Ott Live Ott LiveBigg Boss Ott Live Ott Live
Bigg Boss is tv show by colors tv Hosted by Salman Khan each year, This time in Bigg Boss 15
Salman Khan is again making this season much interesting and can make it on the top in TRP.

<a href="https://biggboss15online.net/ott-episodes/">Bigg Boss Ott Live</a>

======================================

[url=https://biggboss15online.net/ott-episodes/]Bigg Boss Ott Live[/url]
 
Mark StvenMark Stven
Can i embed this code in my Bundaberg Chiropractor (https://www.aaronchiropracticcentre.com/chiropractor-bundaberg) website?
Mark StvenMark Stven
Is it work on my Blood flow restriction (https://www.proformancetherapyandwellness.com/bfr-blood-flow-restriction.html) website which provide health related solutions.?
Andray GaustAndray Gaust
I want to add this on my Hypnotherapy Singapore (https://www.innerchildclinic.com/) website for accurate data of my patient.
Jessica Smith 23Jessica Smith 23
It worked on my Clothing and Shoes website https://oncloudshoes.store/,
https://lameloballshoes.store/.
 
Ryan James 8Ryan James 8

can i add this to my wordpress website : https://juicerhunter.com/how-to-cut-onions-for-kabobs/  (https://juicerhunter.com/how-to-cut-onions-for-kabobs/ )
Leo BarnoskiLeo Barnoski
Owais khan 14Owais khan 14
Im adding my shoes wordpress site On Cloud Shoes (https://on-cloud.shoes/).and https://on-cloud.shoes/store/
https://kaptaanchapal.shop/

 
Owais khan 14Owais khan 14
just sharing site parts knock  (https://partsknock.com/)