I believe this has to do with how Tableau handles data densification, which varies depending on the mark type. Try using an area chart.
This demonstrates the issue with fake data. See also attached.
See this thread http://community.tableau.com/thread/129861
Joe Mako may be able to elaborate on the technical reasons why densification is handled differently depending on the mark type.
running_sum_nulls.twbx.zip 49.4 KB
2 of 2 people found this helpful
Thanks Pedro Machado
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.
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.
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]
GROUP BY Int([Sheet1$].[n] / Val(1)),
Let us know how it goes.
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.
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.
Can you believe this is still valid and required??
Many, many thanks for this
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.
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
Visual DJ Ltd
Tel: (44)/(0) 7957 098 459
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