4

I am having some trouble with a happiness meter formula field based on NPS score in our surveys.

Here is what I have so far:

IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"), 
IF( NPS__c <= 8.99 || NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"), 
IF (NPS__c <= 6.99 || NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"), 
TEXT(1) 
)))

Promoter and Neutral work for these ranges. But the check for Detractors returns the Neutral image. I see that || is evaluating AND. Any ideas on a better approach?

2 Answers 2

6

Try this

IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"), 
IF( NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"), 
IF( NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"), 
TEXT(1) 
)))

The function IF behaves as follows IF(logical_test, value_if_true, value_if_false)

In your example, suppose NPS__C is = 1

Your first test is IF (NPS__c >= 9 .... this is not true, so we go to the value if false

For that you have IF( NPS__c <= 8.99 || NPS__c > 7 .... which is saying is NPS__c less than or equal to 8.99 or is NPS__c greater than 7. The value 1 satisfies this as true, because 1 <= 8.99 and so you get the "neutral" image instead of the "Detractor" you wanted.

2
  • 2
    That's simplest way !! :)
    – C0DEPirate
    May 5, 2016 at 14:51
  • 2
    Also, this avoids situations where the field is say, 8.995, which is more than 8.99 and less than 9.
    – sfdcfox
    May 5, 2016 at 15:33
2

The problem is that in your check on Neutral, you as is the NPS < 8.99 OR > 7. A value of 5 (for example) would be less than 8.99 so the or evaluates true.

I would write it this way:

IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"), 
IF( NPS__c >= 7, IMAGE("/resource/Misery05", "Neutral"), 
IF( NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"), 
TEXT(1) 
)))

It won't reach the next check unless it fails the first criteria (you don't need the < 8.99 check). The other option is to use && instead of || as follows:

IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"), 
IF( NPS__c <= 8.99 && NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"), 
IF (NPS__c <= 6.99 && NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"), 
TEXT(1) 
)))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .