-
1. Re: NPS trouble when one group is all null values
Jim DehnerFeb 12, 2018 10:35 AM (in response to Dennis McGinley)
1 of 1 people found this helpfulHi 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.
-
2. Re: NPS trouble when one group is all null values
Zach Ferris Feb 12, 2018 10:43 AM (in response to Dennis McGinley)1 of 1 people found this helpfulWhat 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.
-
3. Re: NPS trouble when one group is all null values
Zach Ferris Feb 12, 2018 10:51 AM (in response to Jim Dehner)ZN is the easiest way to do the same thing
-
4. Re: NPS trouble when one group is all null values
Dennis McGinley Feb 12, 2018 11:39 AM (in response to Zach Ferris)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
Dennis McGinley Feb 12, 2018 11:47 AM (in response to Zach Ferris)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
Zach Ferris Feb 12, 2018 12:20 PM (in response to Dennis McGinley)1 of 1 people found this helpfulI would put NPS Question on filters and exclude 0s. I figured all the 0s would weight your answer in a negative way.
-
7. Re: NPS trouble when one group is all null values
Zach Ferris Feb 12, 2018 12:22 PM (in response to Dennis McGinley)If this helps, can you mark the question as answered or mark my answers as helpful/correct answer? Please and thank you
-
8. Re: NPS trouble when one group is all null values
Pragathi Bakka Feb 12, 2018 12:31 PM (in response to Dennis McGinley)1 of 1 people found this helpfulHow 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
))
-
9. Re: NPS trouble when one group is all null values
Jim DehnerFeb 12, 2018 1:57 PM (in response to Zach Ferris)
1 of 1 people found this helpfulThat 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
-
10. Re: NPS trouble when one group is all null values
Dennis McGinley Feb 12, 2018 3:28 PM (in response to Jim Dehner)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
Jim DehnerFeb 12, 2018 4:08 PM (in response to Dennis McGinley)
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>