11 Replies Latest reply on Jun 15, 2016 7:43 AM by Mercedes Cobas

# Replace null with empty space in measure

Hi,

I have measure field(Amount) and want the field to be "EMPTY SPACE" when NULL is given .I know you can simply create a calculated field saying

Ifnull(str([Amount]),'')

But I don't want it to be converted to string .Is there a way we can a Empty space or no data  Instead of NULL for a measure value

Thanks,

Rahul

• ###### 1. Re: Replace null with empty space in measure

Rahul,

In what case exactly does it give you a problem. The only time is display the null is when the measure is displayed as a discrete value. In this case it doesn't really matters if it is a string. You could make of copy of your measure with the ifnull function  and convert it as a dimension  for  discrete usage, and keep the int measure as a measure for computing.

Michel

• ###### 2. Re: Replace null with empty space in measure

Hi Michael,

Yes, I am changing Measure to Discrete for some reason and I want it to be discrete.

Thanks,

rahul

• ###### 3. Re: Replace null with empty space in measure

Hi Michael,

The Problem here is there is sum for the measure column sum([Amount])

I am creating a calculated field

Ifnull(str(sum (Amount]) ),  ' ')

I am able to remove nulls But if its not null the result is not correct

Expected                          Getting

12262.12                         12262.1199999999

2606.51                           2606.5100000000000

Thanks,

Rahul

• ###### 4. Re: Replace null with empty space in measure

Rahul,

can you upload a workbook because I don't understand what exactly   you are trying to do.

If you want to aggregate [Amount]  , just use the normal [Amount] measure,  and if you want to display discrete values, then use the calculated field that return the string values with the null removed.  Have you checked the workbook I attached...

• ###### 6. Re: Replace null with empty space in measure

You can use the ZN function to return 0 for Null values.

Consider using Lookup() to find if the cell does not have data (Null).

ZN(LOOKUP(Sum(Amount),0))

Refer the following link for using ZN:

http://kb.tableau.com/articles/knowledgebase/populating-empty-cells-with-zeros-existing-data

• ###### 7. Re: Replace null with empty space in measure

Hi,

Follow the syntax which is in image below.

Null is replaced by two empty spaces.

• ###### 8. Re: Replace null with empty space in measure

Hi Rahul,

change the number format and get the expected format.

Thanks

sankar

• ###### 9. Re: Replace null with empty space in measure

Hi Rahul,

If you need to play around with formatting that number converted to string i.e. round up, add a K for '000s or dollar sign etc. try the following:

IFNULL("\$"+STR(ROUND(SUM([Amount)]),-3)/1000)+"K"," ")

Hope it helps,

Gaetan

• ###### 10. Re: Replace null with empty space in measure

Hi Rahul,

The way I do it is to :

1. create a new calculation field to convert null to 0 with ifnull(sum([Amount],0)

2.  and then right click the calculation field and go to default properties --> number format--> custom and type in #,##0.00; -#,##0.00;""

This way your field remains as floating number data type, and you hide the null values with empty.

Hope it helps,

Louisa

3 of 3 people found this helpful
• ###### 11. Re: Replace null with empty space in measure

This worked out perfectly good ! thanks !!

### Option 1: Display Zeros as Blanks

1. Right-click the field to that you want to format, and then select Default Properties > Number Format.
2. Select Custom, enter #,##0.000; -#,##0.000;"", and then click OK.