7 Replies Latest reply on Aug 23, 2012 6:39 AM by Jonathan Drummey

    How to visualize burn rate in a stacked bar graph

    Nathanael Rosidi

      I am interested in creating a time series stacked bar graph. However, with the client's current data structure, I am lost as how to do so. Briefly, the current data structure does not seem to provide enough granularity to create a stacked bar graph over time. 

       

      I have attached a dummy excel sheet with the client's current data structure in the first sheet. The second sheet is the work-around solution I was able to implement. The tableau file attached is a representation of what I'd like to create (it was created with the work-around data structure) but am clueless as to how to implement this visualization with the  data structure in the first sheet.

       

      Thanks in advance!

        • 1. Re: How to visualize burn rate in a stacked bar graph
          Jonathan Drummey

          Hi Nathanael,

           

          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.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How to visualize burn rate in a stacked bar graph
            Nathanael Rosidi

            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.


            Thanks again,

            Nate

            • 3. Re: How to visualize burn rate in a stacked bar graph
              Jonathan Drummey

              Hi Nate,

               

              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.

               

              Jonathan

              • 4. Re: How to visualize burn rate in a stacked bar graph
                Nathanael Rosidi

                This answers the question. Thanks so much Jonathan!

                • 5. Re: How to visualize burn rate in a stacked bar graph
                  Nathanael Rosidi

                  Hi Jon,

                  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...

                   

                  Thanks!

                  • 6. Re: How to visualize burn rate in a stacked bar graph
                    Nathanael Rosidi

                    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!

                    • 7. Re: How to visualize burn rate in a stacked bar graph
                      Jonathan Drummey

                      Hi,

                       

                      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$]

                       

                      Jonathan