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?

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
    That's simplest way !! :) – C0DEPirate May 5 '16 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 '16 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) 
)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.