I have looked high and low and can't find anyone discussing this.
I have a dataset covering seven years (2013-2019), and I'm looking at the number of records for four places: Example, Fake, Nonsense, and Test. (Obviously my real data is much more complex but I made this dataset to demonstrate the problem because I can't share the real data).
One of these four places, Test (green), has no data in 2013 or 2019. The other places all have at least one year with zero records, but always between 2014-2018. When I plot this out on a line chart, I can use Tableau magic to get the missing values for these places to show up for the years 2014-2018 no problem, but no matter what I do, Test's line starts in 2014 and ends in 2018 (i.e. it will not plot 2013 and 2019 at zero). Show Missing Values doesn't do it, Show Empty Columns doesn't do it, ZN doesn't do it, IFNULL doesn't do it, LOOKUP doesn't do it.
How can I get the first and last datapoints in a line chart to plot as zero when there is no data in the underlying dataset? I'm on 2018.1.
Hi Sebastian,
There are several approaches that might work:
- Use a Previous_Value() table calculation to carry values forward & backward, replacing with 0 where the value doesn't exist
- Pad the data either with a cross-database join, Custom SQL, or some other method to include the missing dates in your data set (what back-end database / file are you using)
- Enable some form of data densification
Technically all of these approaches are data densification, but the first and last are forcing Tableau to do it, while the second is you doing it manually.
The third option might be the easiest and can be done something like this:
Change the date to Discrete and keep Show Missing Values:
Because your measure is a table calculation, it forces Tableau to densify the data for the discrete dimension. Notice that the line now extends to the extreme years:
You can simplify the table calculation a bit. I was able to use:
ZN(LOOKUP(SUM([Number of Records]),0))
Hope that helps!
Joshua
