1 of 1 people found this helpful
As you surmised, Tableau won't draw marks unless it has data. To pad out the data set, I created an additional worksheet in the Excel data source with a list of months, then created a data source using Custom SQL and a cartesian join to pad out the data so instead of just 3 rows, there is now a row per project per month. Then it took a couple of calculated fields to identify the length of the project and the monthly value. See the attached workbook for details.
Thanks Jonathan! This is definitely a better solution than what I came up with. Do you think there is a way to generate that list of dates within Tableau so that I do not need to connect to an excel data source? If not, no worries, your solution is great.
You didn't specify what the underlying database is, so I can't give you an exact answer. There are methods to do this entirely in SQL in Tableau using Custom SQL or in the database query, but they are dependent on the data source. Here are some examples from StackOverflow: http://stackoverflow.com/questions/6588738/create-list-of-dates-a-month-apart-starting-from-current-date.
The easiest solution is often what I initially posted, where you set up a table with a row for each month in your database and then set up the query (in the database or Tableau Custom SQL) to do the cross product.
This answers the question. Thanks so much Jonathan!
One other question, as I am trying out this solution on real data now. I am still connecting to an excel spreadsheet and added a date tab with months listed. What is the Cartesian Join function I need to write? I am not very good with SQL...
Forgot to add that I have 2 other tables that need to be joined. However those 2 other tables have 1 common column header with the main table that can be joined using an inner join. I'm not sure how I would write a sql statement that takes into account 2 inner joins and 1 cartesian join.
Thanks for the help!
Here's the easiest way I can think of. You use Tableau's point and click to auto-generate the SQL for the inner joins, then turn that into Custom SQL to add the cartesian join.
- Connect to your Excel workbook.
- In the Excel Workbook Connection dialog, highlight the first table.
- Click on the Multiple Tables radio button.
- Add the next two inner join tables and set those up. See http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/joining_tables.html for details.
- Then, back in the Excel Workbook connection window, click Custom SQL, then the "..." button that appears to open up the dialog and give you some more space.
- Add parentheses around everything after the FROM. This causes the SQL interpreter to treat the results of the three tables and two inner joins to be one table.
- After the closing parentheses, add a comma and the name of the months table (or table you want to cartesian join)
- In the SELECT portion of the query, add a comma after the last field, then add another row for the field(s) from the cartesian join.
Here's the SQL from doing this on a slightly modifed SuperStore Sales workbook that has a Months worksheet added to it:
SELECT [Orders$].[City] AS [City],
[Orders$].[Customer Name] AS [Customer Name],
[Orders$].[Customer Segment] AS [Customer Segment],
[Orders$].[Discount] AS [Discount],
[Orders$].[Order Date] AS [Order Date],
[Orders$].[Order ID] AS [Order ID],
[Orders$].[Order Priority] AS [Order Priority],
[Orders$].[Order Quantity] AS [Order Quantity],
[Orders$].[Product Base Margin] AS [Product Base Margin],
[Orders$].[Product Category] AS [Product Category],
[Orders$].[Product Container] AS [Product Container],
[Orders$].[Product Name] AS [Product Name],
[Orders$].[Product Sub-Category] AS [Product Sub-Category],
[Orders$].[Profit] AS [Profit],
[Orders$].[Region] AS [Region],
[Orders$].[Row ID] AS [Row ID],
[Orders$].[Sales] AS [Sales],
[Orders$].[Ship Date] AS [Ship Date],
[Orders$].[Ship Mode] AS [Ship Mode],
[Orders$].[Shipping Cost] AS [Shipping Cost],
[Orders$].[State] AS [State],
[Orders$].[Unit Price] AS [Unit Price],
[Orders$].[Zip Code] AS [Zip Code],
[Returns$].[Order ID] AS [Returns$_Order ID],
[Returns$].[Status] AS [Status],
[Users$].[Manager] AS [Manager],
[Users$].[Region] AS [Users$_Region],
[Months$].[Month] AS [Month]
FROM (( [Orders$]
INNER JOIN [Returns$] ON [Orders$].[Order ID] = [Returns$].[Order ID] )
INNER JOIN [Users$] ON [Orders$].[Region] = [Users$].[Region]), [Months$]