9 Replies Latest reply on Jul 26, 2018 10:32 AM by Steve Adams

# Creating a running sum with missing data

It appears from an older post ( Tableau 7.0 Extending Table Calculation) that this functionality has come and gone.  I am trying to plot the running total (ie year to date) of some product sales.  A particular product runs out of stock part way through the year but then reappears at the end of the year.  On the graph, the marks for that product disappear completely during the time of no sales because there are no records in the data.  The data table however shows the results I am expecting.  What's going on?  Is there a work around?  I've tried several formulas to convert the nulls to 0's but haven't had any luck.

- running_sum(sum(ifnull([Dollars],0)))

- running_sum(sum(zn([Dollars])))

• ###### 1. Re: Creating a running sum with missing data

Sam,

I believe this has to do with how Tableau handles data densification, which varies depending on the mark type. Try using an area chart.

Joe Mako may be able to elaborate on the technical reasons why densification is handled differently depending on the mark type.

Pedro

2 of 2 people found this helpful
• ###### 2. Re: Creating a running sum with missing data

There are a number of different types of Data Densification in Tableau, and some of them can happen at the same time, adding to the complexity.

To use Data Densification awareness of the 4 Pill Types of Tableau is fundamental:

- Discrete Dimension

- Continuous Dimension

- Discrete Measure

- Continuous Measure

For your situation, you want the following:

- a Bar mark type

- a Dimension pill on the Marks card

- a Discrete Dimension on the Columns shelf (potentially a Date data type)

- a Continuous Measure on the Rows shelf

Any variation to this may have additional impact. This is why Data Densification is non-user friendly, there are too many exceptions and factors to list in a single comment here.

The attached workbook offers a couple of potential routes to get the results you are looking for.

- "Bin Show Missing" uses a bin on an number to ensure regular intervals from the smallest value to largest value at the bin interval. I call this method, "Show Missing Densification on a Range Aware Pill"

- "Date Domain Complete" this uses a Discrete Dimension pill that is from a date data type field. When this pill is used as the only dimension for compute using, Tableau will perform "Domain Completion Densification". This means for every potential combination of dimension values that exist, we get a mark. This not a not a range aware densification, see next for what that means

- "Filtered Date Domain Complete" this is the same densification method as the prior, but here we can see what happens when some dates to not exist in the data, since they are filtered out.

- "Filtered Date Show Missing" is also a "Show Missing Densification on a Range Aware Pill", the date pill is range aware because of how it is configured, and the Show Missing Values option adds marks for those missing.

The two options that I would recommend would be either the "Bin Show Missing" or the "Filtered Date Show Missing"

This is NOT a complete list of all Data Densification routes. If you or anyone would like to discuss the details of data densification in Tableau, including why mark type impacts densification, you are welcome to email me, found in my profile, and we can meet for a screen sharing session.

2 of 2 people found this helpful
• ###### 3. Re: Creating a running sum with missing data

Thanks Pedro & Joe.  I see how I can work around this to get the results I need.  I  like the Bin method as my intervals are not date type.  Hopefully on my data set this isn't too calculation expensive.

Truthfully though I'm not sure why I need a work around.  I understand the handling of this type of data has flip-flopped over the last few versions and I leave it to the Tableau folks to land on the best solution.

You win some and you lose some.

Thanks,

Sam

• ###### 4. Re: Re: Creating a running sum with missing data

Thanks, Joe!

Sam,

The bin calculation should not be very expensive. The calculation is handled by the database and only the aggregated results are returned to Tableau.

Here the SQL it generated for Joe's example:

SELECT Int([Sheet1\$].[n] / Val(1)) AS [n (bin)],

[Sheet1\$].[t] AS [none:t:nk],

SUM([Sheet1\$].[v]) AS [sum:v:qk]

FROM [Sheet1\$]

GROUP BY Int([Sheet1\$].[n] / Val(1)),

[Sheet1\$].[t]

Let us know how it goes.

Pedro

• ###### 5. Re: Creating a running sum with missing data

Joe,

Any recommendations on how to fill missing rows that are prior to the first data mark?  In your attached workbook (Filtered Date Show Missing), if you deselect the first week the first column becomes week 2 but if you deselect week 2, week 2 remains on the visualization and starts with week 1. Ideally I need week 1 to always display. Any recommendation on how to fill missing values as defined by the axis range, not the first record of data?

I worked around this issue by building a domain table with all possible values (including non-values = 0) but that resulted in over 80M rows, it would be great if Tableau could manage Nulls across a defined domain.

• ###### 6. Re: Creating a running sum with missing data

Paul,

You are welcome to email me, found via my profile, and we can setup a time a to meet for a screen sharing session and would through your situation together.

• ###### 7. Re: Creating a running sum with missing data

Joe

Can you believe this is still valid and required??

Many, many thanks for this

Steve

• ###### 8. Re: Creating a running sum with missing data

Steve,

Yes, these are the fundamentals of Tableau, and I do not expect it to change anytime soon. There is a great deal of complexity in Tableau.

• ###### 9. Re: Creating a running sum with missing data

Hi Joe

I enjoy finding these workarounds. However I was a little surprised that if I can “show missing values” that the running_sum table calc doesn’t give me an option to use them. But I’m probably missing something in the underlying mechanics.

Once again, many thanks

Steve

——

Senior Consultant

Visual DJ Ltd

Tel:      (44)/(0) 7957 098 459

www.VizDJ.com<http://www.VizDJ.com>

www.TableauTraining.co.uk<http://www.TableauTraining.co.uk>

Disclaimer

This e-mail is confidential and may also be privileged. If you are not the intended recipient please notify the sender IMMEDIATELY and remove the e-mail from your system. You should not copy the e-mail or use it for any purpose or disclose its content to any other person. E-mails are susceptible to interference.  You should not assume that the contents originated from Steve Adams or Visual DJ Ltd or that they have been accurately reproduced from their original form.  Visual DJ Ltd accepts no responsibility for information, errors or omissions in this e-mail nor for its use or misuse nor for any act committed or omitted in connection with this communication.  If in doubt, please verify the authenticity of the contents with the sender.

This Email was sent by Visual DJ Ltd. Registered Office: 332 Marsh Lane, Erdington, Birmingham, West Midlands, United Kingdom, B23 6HP

Registered in England No. 10616287