What is your data source? There are several approaches to solving this kind of problem, but almost all are going to involve merging the date fields into a single field that you can use as a single dimension. For example you might:
- Pivot the data on the two date fields such that you have a single column for date and another column indicating whether the date is expected or received
- Union the data (using Custom SQL or Tableau's union ability) to itself so that you have two rows for each unique file name so that you can use one row for the expected and one row for the received with a single date representing both.
- Use data blending with a master (scaffolding) data source that contains a field with every date and then blend to 1 copy of your data set above to get expected and another copy to get received.
Generically, I would suggest them in the order listed above. But It all depends on your data source as to what is possible.
Hope that helps!
Many thanks for your very informative feedback.
The data source resides in one SQL table. One option I did try was to create an Excel file with two columns: one listing all dates and a helper column full of 1's. Then I created a calculated field in Tableau with just the number 1 and tried some sort of linking/blending, but to no avail.
I could go down the road of just creating two separate dashboards for each dimension, but I'd rather find a solution to keeping this all in one visual.
I'm interested to learn more about your options 2 and 3. Could you expand a little?
Many thanks again.
Sure! So, let's just consider option #2 for now, because #3 really is a last resort kind of thing that introduces a lot of complexity.
Since you are using SQL Server, then you should be able to use a custom SQL statement to union together two copies of the data to give you what you need. Tableau 10.2, in beta now, will allow you to union together data without using custom SQL, but for now, it the SQL script will look something like:
SELECT [Unique File Name], [Date File Received] AS [Date], 'Received' AS [Expected or Received] FROM Table UNION ALL SELECT [Unique File Name], [Date File Expected] AS [Date], 'Expected' as [Expected or Received] FROM Table
This will give you a data set very similar to the one based on Excel above. You'll have two rows per file with one date field and an indicator for which row represents received and which represents expected. That will make it very easy to generate the desired view in Tableau.
Hope that helps!
Hi Joshua and my apologies for not replying sooner.
I have not had the opportunity to try this as yet, but will do at some point very soon.
Many thanks again for your feedback.