Generally if you want Tableau to draw a mark at a point, you have to have that combination of dimensions that will be that point in your data source. Tableau will not pad your data source, so if you want it padded with null values, you will have to do that before you connect Tableau to your data.
There are a few other options to help you pad your data with null values:
- Use a data blend, with the primary data source one that has every possible combination of dimensions that will make a mark, then if there is a value missing in your data source, a null value will be placed.
- Use Custom SQL, varies by situation, could be a UNION, and/or a CROSS JOIN to generate the combinations of data that you want.
- In some cases, you can use a table calc to pad your data when using a crosstab layout.
Can you mock up what you want your end result chart to look like, and provide a sample data set or workbook?
Joe, last night at home I was thinking about my issue and realized -- duh! -- that after I had looked at the underlying data that there was no data for those missing points! So you're suggestion of padding the data manually is spot on to what I'm thinking.
I figured out something yesterday in Tableau that I had thought was not possible so I'm going to attack it this problem again now that I know what my issue is.
~Tableau Padawan ;-)
I have the same issue--I want to show data across a continuous time period--but I don't have any actual numbers in my dataset. I'm using "count distinct" to display how many unique workshops were offered each year. One year has no workshops, but I want it to show up on the graph as zero. Tableau is just eliminating the year from the graph. Suggestions?
jwode, can you provide a sample packaged workbook that demonstrates your situation?
I've attached a sample packaged workbook. When all workshops are counted, you can see every year from 2005 to 2009. When only Photography workshops are counted, however, 2007 disappears because there were no Photo workshops that year. I don't see a way to make 2007 show up on the graph with a count of 0. Thanks for any help you can provide.
example_problem.twbx 75.3 KB
There have been numerous threads discussing this over the years.
Have a read of this thread - I posted an example here to try to explain what was happening and Joe followed up with a modified version demonstrating one approach to filling in the gaps.
Joe says this approach "does require an understanding of SQL" which unfortunately I do not yet have, though it is a goal for the future. I'm disappointed something this basic doesn't seem to be built in. It seems that, for a time-series graph, users should be able to right-click the axis and select, "Show all months [weeks, etc.] in between start and end dates."
You could also approach the situation from another angle, with a parameter and a calculated filed like:
IIF([Department]=[Department Parameter] OR [Department Parameter]="All",[Number of Records],0)
This is done in the attached.
example_problem_jm_edit.twbx 82.1 KB
Hope it's not too late for me to join the party. Maybe Richard and Joe are looking for some nostalgia?
I'm having the same problem. I have a line chart where it displays the number of total logins (login_count.png) and number of unique logins (weekly_unique_id.png) per week. In case it's not clear, I determine the weekly unique logins by combining the UserName and Week Ending field and then do Count Distinct.
As you can see, I'm having a problem with gaps in the data (sheet_view.png). I know I need to pad the data. I'm just not sure how to go about doing it. Unfortunately, I can't attach my workbook due to client confidentiality (it's also QUITE large). But if there's a specific request in terms of data structure, I'll do whatever I can to answer it or sanitize the data to make it easier to help me out.
Thanks in advance for the help!
You can wrap your aggregations inside of the ZN and LOOKUP functions like:
and that will add zeros because of your crosstab layout.
Toby, you should try this in Tableau 7.0. Does it do the right thing now?
Senior Software Engineer
I have Tableau 7.0. How can I show null values in my graphs? I have an area graph that looks rather silly (see image) because two of the five cohorts didn't have anyone who said "Somewhat important" (green), and only the last cohort said "Only a little important" (orange). I would like to fill in the area even if it disappears at the exact point there are no responses.
Hi . Jwode,
I think you just need to tell Tableau to display nulls at the 'Default Value' (in this case, 0). See:
Let me know if that doesn't work.