5 Replies Latest reply on Jul 7, 2014 1:13 AM by Sean Arnold

# get area graph to show zero the day before a data series starts

I have an area graph which looks like this:

You'd think that the orange area indicates something significant that happened.

Actually the significant thing that happened emerged on the most recent day of the analysis (the right hand side) and is an effectively invisible vertical line on the right - the rendering makes it look like the orange data set is to blame.

Short of either post-processing the data to insert "zero-s" or just using a stacked bar graph instead of an area graph, is there any way to make Tableau area graphs insert a zero on the day PRIOR to a new data series starting to show it ramping from the previous day, rather than suggesting that something important occurred in an adjacent but unrelated data series?

• ###### 1. Re: get area graph to show zero the day before a data series starts

after an exchange with Tableau's excellent customer support I've found that this is a fact of life in current Tableau area graphs - he entered a feature request for it, but prior to any change I guess folks should be aware of this particular quirk as it might lead to confusions as it did for us...

• ###### 2. Re: get area graph to show zero the day before a data series starts

Simon, there are a few routes to achieve what you are looking for.

One option if your continious pill on the Columns shelf is a Date field, you can use a formula like

ZN(LOOKUP(SUM([value]),0))

and then use the date pill as the compute using, Tableau will pad the data, adding zeros.

see the attached for a before and after example.

Please forward this to your Tableau support ticket so they can be informed as well.

• ###### 3. Re: get area graph to show zero the day before a data series starts

We have used the formula posted (originally found in other posts) but have seen what we class as odd behaviour.

Given the data set below:

Store B did not sell Product A on the 2nd and 5th where Store A did. Assuming that the user has filtered the date range to the 2nd - 6th (inclusive), they then filter to show only Store B, the 5th is correctly padded with a 0 due to it being in the middle of a dataset available to Store B where as the 2nd disappears from the graph. It is the same scenario (no data as opposed to null) but because its at the extreme ends of the dataset it doesn't seem to work.

Do you know of a way around this issue in Tableau? Obviously we can pad the data source with 0's but the more dimensions you have means the more 0 artificial rows you will need to cover all combos of user filtering (plus you don't need to use the lookup formula)

Example workbook attached...

• ###### 4. Re: get area graph to show zero the day before a data series starts

Hi Sean,

I'm not sure there's a Tableau-only solution that will work well for you. The basic issue that you discovered is that Tableau only pads out between the range of values that are in the data at hand. In order for Tableau to always pad out the data, that means that we'd have to ensure that beginning and end of the range are available to Tableau at the time Tableau does densification, which is prior to table calculations. Often we can use a table calculation filter to remove the values we don't want, however in this case the Store is *not* in the view, so a table calculation filter on Store won't work. Sometimes we can bring the dimension we want to use as a table calc filter and then change the aggregations in the view, however table calculations are completed before table calc filters are applied, so there's no way for the table calc filter to change the values of what is displayed. If the table calc filter on Store was a single-select filter, we could get a desired result, but you set up the Store filter as a multi-select so that won't work. Another potential issue with this method is that table calc filters are specific to the worksheet, so it's not possible to have a scoped or global table calc filter that would cover multiple worksheets on a dashboard.

It might be possible to get what you want using a Tableau data blend where the primary source is padded out and the secondary source is filtered on the Store dimension, however this runs into the issue where the filter on the secondary source always has a Tableau-imposed Null value in it, and there are also limitations for using scoped & global filters in dashboards.

So, your best bet is to do some form of padding. There are several ways to do this:

1) pad out so there is a combination for every possible dimension***, for every record.

2) pad out so there are combinations for every possible dimension***, at an aggregate level.

3) create a scaffold data source that has the combinations for every possible dimension***, then use that as the primary data source and blend your data to that.

*** When it comes to dates, you do have the option of only padding out two records for each combination of other dimensions (one for the min date and one for the max date), then you can use Show Missing Values to fill in everything else.

If you are padding out thousands or even hundreds of thousands of records, maybe even millions of records, the performance of a Tableau data extract is such that the difference might not even be noticeable, and it's certainly possible to tune other data sources to be very fast.

• ###### 5. Re: get area graph to show zero the day before a data series starts

Thanks for the reply Jonathan. In one of my examples it takes the row count from 3 million rows to 40 million rows and while we don't see much in terms of difference once the extract is compiled, we do see it in compiling the extract.

When you stated...

*** When it comes to dates, you do have the option of only padding out two records for each combination of other dimensions (one for the min date and one for the max date), then you can use Show Missing Values to fill in everything else.

I immediately looked to pad from an earlier date on Store B rather than pad the 2nd July, however as you can see the earlier date isn't in scope due to the filter and therefore still doesn't pad out the 2nd. Is this what you were trying to state? Do I have to do something extra to achieve the min & max approach?

Updated example attached (in 8.2 format now)