11 Replies Latest reply on Feb 12, 2018 4:08 PM by Jim Dehner

# NPS trouble when one group is all null values

Hello Tableau Experts,

I'm running into a silly (good for business) problem when calculating NPS (Net Promoter Score) in a dashboard.

Context: Participants are asked on a scale from 1-10 how likely they are to recommend an experience to a friend or colleague.

9-10 = Promoter

7-8 = Passive

1-6 = Detractor

NPS = Promoters - Detractors / Total Respondents

Current working equation:

((SUM(IF[NPS question] >= 9 THEN 1 END) - SUM(IF[NPS question] <= 6 THEN 1 END)) / SUM(IF[NPS question] >= 1 THEN 1 END)*100)

Problem: This equation works until one of my values is non-existent in the data.  Specifically, no one has selected a value of 6 or less, so the value simply doesn't show up in the worksheet; I don't get a null or error, it's just blank.

Best,

Dennis

• ###### 1. Re: NPS trouble when one group is all null values

Hi Dennis

try this       ifnull(lookup(sum(NPS question}),0),0)

it should fill the blanks with 0's

you would then use the new formula in you if statements

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: NPS trouble when one group is all null values

What do you mean you don't get a null? If it is blank, doesn't Tableau read it as null? You could create a Calc for ZN([Detractor]) Which turns all nulls into 0s. That might create a problem in regards to weighting the calculation. Perhaps you can exclude 0s in a filter to apply to the calculation.

Furthermore, did you ever think about creating Calculated Fields for each "level." Is there no SQL Key or ID that you can use for CountD([Promoters]) - (CountD([Detractors])/Count([Total Respondents])).

You would just need to create Sets/Calculated Field like pull all NPS Questions >= 9 and highlight, create Set, call it Promoter OR ATTR([NPS Question]) >= 9 and call it Promoters, and go down the line.

That might help.

1 of 1 people found this helpful
• ###### 3. Re: NPS trouble when one group is all null values

ZN is the easiest way to do the same thing

• ###### 4. Re: NPS trouble when one group is all null values

Thanks for your response Zack.  My apologies for mis-conveying that problem.  I meant that no null shows up in the worksheet itself, the data layer shows null values.

But that's exactly where my problem is arising.

Because of the way the calculation is being done, it's looking for a value, but since the value is null, the whole thing shows up as a zero in the worksheet.

I like your suggestion about turning them to zeros, and I guess my denominator would simply be aggregating anything greater than zero.  I'll try this.

• ###### 5. Re: NPS trouble when one group is all null values

Hey Zach,

So I tried this, and now I'm getting a number back, but it's incorrect.  I'm not sure why it's not returning the correct value.  Did I set this up properly?

((

SUM(ZN(IF[NPS question] >= 9 THEN 1 END))

-

SUM(ZN(IF[NPS question]<= 6 THEN 1 END))

/

SUM(ZN(IF[NPS question] >= 1 THEN 1 END))

*100

))

• ###### 6. Re: NPS trouble when one group is all null values

I would put NPS Question on filters and exclude 0s. I figured all the 0s would weight your answer in a negative way.

1 of 1 people found this helpful

• ###### 8. Re: NPS trouble when one group is all null values

How about using IF THEN ELSE?

((

SUM(ZN(IF[NPS question] >= 9 THEN 1 ELSE 0 END))

-

SUM(ZN(IF[NPS question]<= 6 THEN 1 ELSE 0 END))

/

SUM(ZN(IF[NPS question] >= 1 THEN 1 ELSE 0 END))

*100

))

1 of 1 people found this helpful
• ###### 9. Re: NPS trouble when one group is all null values

That is certainly true if there is an actual record with a null value. But

if there is no record i.e. that partibukar combination of dimensions does

not appear in the data. Then you have to create a record with the lookup

function

Jim

1 of 1 people found this helpful
• ###### 10. Re: NPS trouble when one group is all null values

Hey Jim,

Using that ifnull(lookup(..  equation you listed earlier.  Where exactly does that fit in the equation I listed?

Does that now go on each line?

((SUM(ifnull(lookup(sum(NPS question}),0),0) >= 9 THEN 1 END)

-

SUM(ifnull(lookup(sum(NPS question}),0),0) <= 6 THEN 1 END))

/

SUM(ifnull(lookup(sum(NPS question}),0),0) >= 1 THEN 1 END)*100)

Is that what you're suggesting?

• ###### 11. Re: NPS trouble when one group is all null values

Any place you had your  you would put in your original formula.

I would just make it a separate calculation and drop that name into your

calc.

Jim

On Feb 12, 2018 6:28 PM, "Dennis McGinley" <tableaucommunity@tableau.com>