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.

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.

ZN is the easiest way to do the same thing

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

If this helps, can you mark the question as answered or mark my answers as helpful/correct answer? Please and thank you

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

))

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

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>