8 Replies Latest reply on Dec 9, 2015 12:40 AM by max dl

# How to add a calculation that takes into consideration "show missing values"?

On a simple bar graph my x-axis is a date dimension, but there isn't data for every date.   I'm using the "show missing values" option to show all the dates.    I'd like an average calculation of my measure in the y-axis, but it only averages for the values on dates I have data for.  I'd like to treat those "missing values" as zeros for the purpose of this calculation.     The purpose of this is to just draw an average reference line.   Thanks!

• ###### 1. Re: How to add a calculation that takes into consideration "show missing values"?

Rob, is this what you're looking for?

--Shawn

2 of 2 people found this helpful
• ###### 2. Re: How to add a calculation that takes into consideration "show missing values"?

Hi Shawn (and others responding to posts). It's really helpful when you respond to a post with an attached example workbook also to include a brief summary of what you've done in the workbook. It just means that someone skimming forum threads quickly can get an idea what you've done without bothering to download the workbook and take the time to see what's there. (It can often also help the original questioner figure out what you've done.)

For example, on this question, just a one-liner saying that you've used IFNULL() or ZN() (or whatever you've done - I haven't looked), would just give people a hint. It also improves the chances of forum searches finding past examples of similar topics.

It's still really useful to post a sample workbook - so don't let me deter you from the stellar job you're doing on answering questions and providing samples - just suggesting ways to make them even more useful.  ;-)

• ###### 3. Re: How to add a calculation that takes into consideration "show missing values"?

Hey Richard, I guess I was falling into the Joe Mako style of posting. But I agree, a little more commentary would probably help add to the bigger conversation (see above). Thanks for the suggestion.

Shawn

• ###### 4. Re: How to add a calculation that takes into consideration "show missing values"?

That's great - I'm not sure I'm going to live up to that standard. Embedding images and formatted text and Public workbooks in the posting really works well, doesn't it?

• ###### 5. Re: How to add a calculation that takes into consideration "show missing values"?

Yes Richard, I really like the new embedding features, especially when you click on the image and it gives a crystal clear pop-up image. Very nice way to communicate, and it's relatively quick. FYI: I just edited this one: http://community.tableau.com/message/172278#172278

--Shawn

(PS: I'm still working on the map project. Hope to have something to you soon.)

• ###### 6. Re: How to add a calculation that takes into consideration "show missing values"?

Perfect!  Million thanks for the detailed response.

• ###### 7. Re: How to add a calculation that takes into consideration "show missing values"?

I'm trying to do a similar thing, where I have a simple table with null values, but when adding an average to the end (as a grand total column) it doesn't count the null values and displays the wrong data. Is there a way round this?

• ###### 8. Re: How to add a calculation that takes into consideration "show missing values"?

Thanks Shawn.

To avoid others to download and open the workbook, see what he did here through the screenshot below:

1 of 1 people found this helpful