Show 0 if value has no data - Tableau

Question

I need to classify my dataset into groups depending on sales and store wise details. I'm fetching my data from the database, so it contains null or empty values. How to replace them with 0 for calculation purpose.

How to display 0 if there is no data (null or empty) corresponding to that field in a row?

Answer 1

Hi Abhay,

To replace null or empty values you can use ZN() or ISNULL() to check and show 0.

Using ZN() - 

ZN function is used to return 0 or not null values for a column. It takes a field as a parameter.

ZN([Phone No]) would return phone no if or null else gives 0.

Using ISNULL() -

 ISNULL([Field] returns true if value is NULL.

Hope this helps you.

For an in-depth explanation check out Tableau Training Course details.

Comments

What if the cells are BLANK and not NULL?

---

Aren't blank cells considered null?

---

A null value is not the same a blank.  Null means it is void of anything, whereas blank means it is space.

Answer 2

Now you want the cells without data with "0" right.

1. drop category filed into columns.
2. create a duplicate for catgeory filed in data pane.
3. drag the duplicated filed into rows.
4. Now drag the "lookup with ZN" calculated filed which you created in first filed to text. now you see that the empty cells are populated with zero.

Answer 3

Option 1: Display Zeros as Blanks

1. From the Data pane (where the Dimensions and Measures are displayed), right-click the field that you want to format, and then select Default Properties > Number Format.
2. Select Custom, enter #,##0.000; -#,##0.000;"", and then click OK.

Answer 4

Method 2

1. Select the range with empty cells.
2. Press Ctrl + H to display the Find & Replace dialog box.
3. Move to the Replace tab in the dialog.
4. Leave the Find what field blank and enter the necessary value in the Replace with a text box.
5. Click Replace All.