Regarding ZN function & your ice cream example, how do you factor in if there is no date in the database? Example, if there is no November 12, 2017 data. In other words, I have no sales for November 17, 2017, that date does not exist in my database. But I want to put zero (0) for that date.
Hi! Thanks for reaching out. I'd recommend using the New Rows feature in Tableau Prep to generate the placeholder rows you need. help.tableau.com/current/prep/en-gb/prep_new_rows.htm
Good call, I must have missed this one! Here is the workbook: public.tableau.com/app/profile/eric.parker/viz/IFNULLISNULLZN-DealingwithNulls/NullFunctionTitle
It depends what you want to replace the Null with. For example, if you're trying to calculate tenure of an employee at a company you might calculate the different between a Hire Date and a Termination Date. But if the Termination Date is null for active employees, for the sake of the DATEDIFF calculation you could do something like this IFNULL([Termination Date],TODAY())
@@onenumbertableau sorry I confused you with how I wrote that! I have some measures in percentage format which I need to change the null values with "N/A". It works for measures in number format but not percentage format for some reason.
@@MrLukkil Interesting. The only way I could think to change null values to N/A would actually be to change the output to string (using the STR function). Otherwise, Tableau won't want to allow a text output for a numeric field. I've also seen folks do creative things with the number formatting options which is potentially a route you could explore: onenumber.biz/blog-1/2021/11/28/format-zeroes-as-blanks-or-dashes-in-tableau
Need help with your nulls in Tableau? Schedule an hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909 Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
This was very helpful. Thank you.
I'm so glad it was helpful. Thanks for the support and encouragement!
Thank you. That’s helpful. Can you use if Null with string data
Thank you! Yes, IFNULL() should work with string data too.
I’m having a problem that likely has to do with the very last thing you talked about. Good stuff man.
So glad it helped, thanks for following Nick!
Regarding ZN function & your ice cream example, how do you factor in if there is no date in the database?
Example, if there is no November 12, 2017 data.
In other words, I have no sales for November 17, 2017, that date does not exist in my database. But I want to put zero (0) for that date.
Would like to display dates for all months on a run chart even the sales for a particular month is Zero (0).
Hi! Thanks for reaching out. I'd recommend using the New Rows feature in Tableau Prep to generate the placeholder rows you need. help.tableau.com/current/prep/en-gb/prep_new_rows.htm
Very much helpful
So glad it helped, thanks for watching!
Can you please upload the Data you are working with? If we don't practice when you are explaining to us then it does not benefit us. Thanks
Good call, I must have missed this one! Here is the workbook: public.tableau.com/app/profile/eric.parker/viz/IFNULLISNULLZN-DealingwithNulls/NullFunctionTitle
May i know How to handle null in a date column field ?
It depends what you want to replace the Null with. For example, if you're trying to calculate tenure of an employee at a company you might calculate the different between a Hire Date and a Termination Date. But if the Termination Date is null for active employees, for the sake of the DATEDIFF calculation you could do something like this IFNULL([Termination Date],TODAY())
Replacing NULL for numbers in percentage format? Doesn't seem to work for me.
Hi! Try using decimals instead of whole numbers. So if you want 10% it would be something like this, IFNULL([Measure],.1)
@@onenumbertableau sorry I confused you with how I wrote that!
I have some measures in percentage format which I need to change the null values with "N/A". It works for measures in number format but not percentage format for some reason.
@@MrLukkil Interesting. The only way I could think to change null values to N/A would actually be to change the output to string (using the STR function). Otherwise, Tableau won't want to allow a text output for a numeric field. I've also seen folks do creative things with the number formatting options which is potentially a route you could explore: onenumber.biz/blog-1/2021/11/28/format-zeroes-as-blanks-or-dashes-in-tableau
@@onenumbertableau Thanks! Appreciate the swift reply. I'll take a look!
Need help with your nulls in Tableau? Schedule an hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
Tableau is complicated for no reason
There are definitely situations where I agree! What about nulls makes you feel that way?