1 2 Previous Next 17 Replies Latest reply on Aug 9, 2017 9:48 AM by Prashant Kuruamparambatta

# Replacing NULL Values (Measure) with "n.a." or "-"

Dear All,

I want to replace a NULL value (here as an empty field shown and is results of a calculated field) with an "n.a." or and "-".

Is that somehow possible?

Thanks

Marc

• ###### 2. Re: Replacing NULL Values (Measure) with "n.a." or "-"

Hi Pooja,

thanks for the link which I was following up. But it is not working in this case.

We did create out of measures some calculations. Behind that we have or we have no data (this sometimes happens). What we get as a reply from Tableau we get the attached picture:

As you can see in T/C (number of Telecasts) or DUR cumulated duration in minutes). So these are no dimensions.

This is the whole issue. Wouldn't it be nice to have instead of a workaround an automated solution for NULL values and its replacement like "n.a." or "-" or whatever you would like to put in there...?! :-)!

Thanks

Marc

• ###### 3. Re: Replacing NULL Values (Measure) with "n.a." or "-"

I see. Would a 0 work instead of the string 'N/A or -' if that works you can try

ZN(Your expression), it returns the expression if it is not null otherwise returns a 0. Not sure if this works, but I would think if it is a measure a digit would make more sense instead of a string 'n/a'

• ###### 4. Re: Replacing NULL Values (Measure) with "n.a." or "-"

A “0” tells that there are values. An n.a. tells me that there is no values…. So this is why I want the n.a.

Marc

• ###### 5. Re: Replacing NULL Values (Measure) with "n.a." or "-"

Actually you are trying to replace null values in the Measure Column and "NA", "" are strings, so we have to change the syntax to over come coomom error " Aggregate Non-Aggregate related error."

if isnull([Sales]) then "NA" else str(sum[Sales]) end

The following links are related to replacing nulls.

http://community.tableau.com/message/331331#331331

Adding two columns using create calculated field, which has null values and data is returning Null?

Re: How to include null values in a count calc?

Best Regards

Laxman Kumar

1 of 1 people found this helpful
• ###### 6. Re: Replacing NULL Values (Measure) with "n.a." or "-"

I don't know about Marc - but you solved my problem! Thank you.

• ###### 7. Re: Replacing NULL Values (Measure) with "n.a." or "-"

There is a two-part solution to this challenge:

1) wrap your calculation with the lookup() function.  Ex:  lookup(sum([Calc]),0)

2) right-click the calculation, format (make sure you are editing the 'Pane' option), then under Special Values, type in what you want to see.  In this case NA

Bingo.

• ###### 8. Re: Replacing NULL Values (Measure) with "n.a." or "-"

I have a Custom Grouping pill that I have on Rows called Office. One of the offices has no data therefore that office is not even populating with a row. Is there anyway to show it when no data?

I was going to use this but as you can see I can't select the option:

• ###### 9. Re: Replacing NULL Values (Measure) with "n.a." or "-"

here's a screen shot of what I have going on. There should be an Office 8 as the bottom row with just no bar....

• ###### 10. Re: Replacing NULL Values (Measure) with "n.a." or "-"

Hello Kyle;

If you haven't already done so, check your filters and make sure the Include Null Values box is checked.

You can also try adding ATTR(Projected Sales (axis)) to your filters and making sure the Include Null Values box is checked.

• ###### 11. Re: Replacing NULL Values (Measure) with "n.a." or "-"

AGG(Projected Sales (axis)) was not on filters and Include Null Values was not checked. I checked the box, but it will still not populate. Is it because there is absolutely NO Data...

• ###### 12. Re: Replacing NULL Values (Measure) with "n.a." or "-"

I thought that selecting the Office (Group) filter and using a custom value list and checking the box that said "Include all values when empty" would lock in what Offices would show, but that doesn't work either...

• ###### 13. Re: Replacing NULL Values (Measure) with "n.a." or "-"

The only option that I can think of, off the top, is to create a simple table in Excel that contains each office as a row.  Use this as the primary data source in your workbook and blend to the secondary data source using office as the blend key.  Essentially, a left join toa "lookup" table.  This should give you the results your're looking for.

Hope this helps.

• ###### 14. Re: Replacing NULL Values (Measure) with "n.a." or "-"

So could I get the bottom row to HIDE when one or more columns is blank then? first row is 2015 second row is 2016 (same sheet) and the % is YOY (it's own sheet)

1 2 Previous Next