6 Replies Latest reply on Aug 31, 2016 10:32 AM by Jonathan Drummey

    supply stack charts (supply & demand vs Cost)

    Robert Wade

      Anyone know how to do one of these charts?  Can do in excel, but have not been able to determine how to do in Tableau....

       

      X axis is cumulative supply volume by supplier (total, last bar, is fully market supply potential)

      Y Axis is total cost to supply

       

      Horizontal lines are target, stretch pricing targets.

       

      Vertical red line (way at end) is the market demand

       

        • 1. Re: supply stack charts (supply & demand vs Cost)
          Jim Wahl

          Hi Robert,

           

          You can do this with Tableau's polygon mark type, which allows you to draw your own polygons---rectangles in this case---by specifying the individual points or corners.

           

          For example, in a cars data set with: make-model, quantity sold and price, the data set might look like

          stacked1.png

           

          And you might want to get this view

          stacked0.png

           

          To use polygons, you'd create four corners, each with an x/y value, for each make-model. If you manually did this in Excel it would look like

          stacked2.png

          The corners run counter-clockwise from bottom-left (bottom-right, top-right, top-left).

           

          With a stacked supply chart, the y-value is always 0 for the bottom corners (0 and 1) and equals the price for the top corners (2 and 3).

          The left x-values (0 and 3) equal the previous cumulative quantity and the right values equal the current cumulative quantity.

           

          You could build this view offline in Excel--- and then in Tableau, you'd just put x, y in the view as rows and columns (as continuous dimensions), make-model on the color shelf, select mark type polygon and put corner on the path button / shelf that appears after selecting polygon.

           

          If this is a one-off, it might be easier to do it offline, but it's not that difficult to build this view in Tableau.

           

          First, you need to create four rows for each make-model. You can do this by selecting custom SQL in the data source and using UNION ALL SQL statement that duplicates your data set, and allows you to add the "corner" column 0, 1, 2, 3. ...

          stacked3.png

           

          Now you need to create calculated fields, for x, y, cumulative quantity and order by price. All but Y are table calcs.

           

          Order by price

          stacked4.png

          Since we have four rows / make-model, this table calc looks at the previous price, if it's the same it uses the previous value for order by price. For the first row, the ELSE clause is called and the 0 in PREVIOUS_VALUE(0) returns 0 if there is no previous value. Note that all of the table calcs require aggregates. For this view we're working at the finest grain / level of detail, so there is no difference between MIN(), MAX(), AVG(). But ATTR() has the advantage of returning * if we erroneously have more than one value, which can be handy error checking.

           

          After entering the formula, click Default Table Calculation and select the addressing to be make-model and sort order to be price, ascending. Then click OK, right-click on the new field and select discrete. You can now add make-model to the rows shelf and add order by price to the left of it. (If this doesn't work, make sure order by price is a discrete, blue-pill field).

           

          Double click on price and quantity to add these to the view to get:

          stacked5.png

           

          Cumulative quantity

          stacked6.png

          Again, make sure you click on Default Table Calculation and select advanced > Addressing = make-model, Sort by price ascending.

           

          Double-click to add this to the view.

           

          Y

          This one is easy. IF ATTR([corner]) <= 1 THEN 0 ELSE SUM([price]) END

           

          Double-click to add it to the view. Also add corner, which should be a blue pill discrete dimension, to the view after make-model.

           

          X

          stacked8.png

           

          The confusing part is the compute using. Here we want it set to make-model. Then LOOKUP([qty cumulative], -1) uses the value for the previous make-model. Also note the ZN() is used to return 0 for the first value, when LOOKUP returns NULL.

           

          After adding this last field to the view, we have a result similar to the manually computed Excel output above:

          stacked9.png

           

          And now you can create the graphical view. If it doesn't look right initially, double check all of the table calculations' Compute Using settings. Depending on what order you add the pills, they may use "table" rather than their defaults configured above.

          stacked10.png

           

          Lines

          You had several lines in your graph. These are easy add as reference lines by right-clicking on the y-axis.

           

          Lables

          You may want to add labels to the graph. You can't do this directly with polygons, but you can use a dual axis graph with multiple mark types to add the labels. In the above example, it'd be a little tricky given the varying sizes. I'm out of time for today (and hopefully didn't already insult your Tableau skills with too much detail), but if you want some help with this just ask.

           

          Jim

           


          1 of 1 people found this helpful
          • 2. Re: supply stack charts (supply & demand vs Cost)
            Robert Wade

            Thanks for the walk through!  Not a simple task...will try it and see how well it works...my concern is "refreshing on the fly" based on market selection, etc.....

             

            Just a thought, (probably more for the ideas forum), but could be easily done if tableau had the ability to "automatically" vary the cell width when the bar chart is created.  I can get close with normal bar chart and width is volume, only problem is the cell sizes remain fixed, leaving lots of blank space.

            • 3. Re: supply stack charts (supply & demand vs Cost)
              Shawn Wallwork

              Robert, the forums tend to be a fabulous place for 'how can I do this in Tableau', as Jim provided. But you are also correct, this thread is an Idea candidate. If you post it there let us know so we can vote for it.

               

              --Shawn

              • 4. Re: supply stack charts (supply & demand vs Cost)
                Jim Wahl

                Hi Robert,

                 

                It's not as hard as it looks and it should be robust against filtering. If you have a hierarchy, then it gets a little more complicated---might be easier to just use a worksheet for each level in the hierarchy and switch between them using a parameter in a dashboard.

                 

                I played with the view a little bit more this morning. I cleaned up a few of the table calcs, but the main change is adding labels. I added three labels for each row: x- and y-axis, data-specific tick marks, and mark labels for annotation. I can then create an (x, y) label position for each and use another calculated field to specify the text. 

                 

                Here are a few pointers. I can provide more detail, if you'd like

                1. Custom SQL

                Needs to be modified with an additional row for each label, using UNION ALL statements. In my example, each car (make-model) gets three additional rows for the thee labels (x-axis, y-axis, mark1). The key here is that the rows with corner numbers must have null label values; and the label rows must have null corner values. Trying to combine labels and corners results in extra data points and problems when using dual-axis.

                 

                You want the data to look something like this. Notice the nulls.

                stacked11.png


                 

                2. Calculated fields for label position

                I added two new calculated fields (x label and y label) to specify the coordinates for each label. I think these fields are self-explanatory, including some of the fiddly positioning. For example, the x label position for "y-axis" labels is 2.5% to the left of the y axis.

                 

                3. Calculated field for label text

                This is easy in theory and a bit tedious in practice. For example, I hide the first label, if the bar width is < 10% of the x-axis. Otherwise it overlaps with the y-axis labels.

                 

                4. Adding pills to view

                Drag "x label" and "y label" to the columns and rows shelves, respectively---before the x and y pills for the polygon chart. In a dual-axis chart, the right pills are visually on top of the left pills---so the polygons will hide mark labels that are underneath. This is fine. I only use one mark label and that's for the x-axis tick marks, and since I'm using a the built-in Tableau shape "thin arrow," I want the upper half of the arrow to be hidden.

                 

                Once the pills are added, double check all of the table calcs compute using settings. You should now be looking at two charts and it's easiest to debug them when they're separate.

                 

                After you're happy with the layout, you can select dual-axis on each and synchronize axis. Now you should have one chart, similar to

                stacked13.png

                 

                5. Remove NULL warnings.

                Since there are a lot of nulls in the Custom SQL data set, I like to click on each pill in the Columns and Rows shelf > Format > Pane > Special Values > Hide (Break Lines).

                stacked12.png


                6. Format labels and hide axes

                The labels should be centered horizontally and vertically. I've used the Seqoe UI font throughout, which I find works best on screen displays.

                 

                7. Use dashboard to add axis labels, ...

                I prefer to have horizontal axis labels and the easiest way to add these is with floating text boxes on a dashboard.

                 

                I also used floating blank boxes on the dashboard to hide the negative axis ends.

                 

                Finally, I added a table to show the prices and quantities. I applied the Foreign or Domestic filter from the graph view to the table as well, so they are synchronized. You can do this by right clicking on the quick filter > Apply to worksheets.

                 

                stacked14.png

                See attached or the live version @ Workbook: stackedSupplyChart.

                 

                Jim

                 

                 


                • 5. Re: supply stack charts (supply & demand vs Cost)
                  Jim Wahl

                  This just got a lot easier with Tableau 10's bar chart sizing! No custom SQL, just a single table calc.

                   

                  Redoing the above example of price vs quantity of cars sold.

                   

                  Here's the data source:

                   

                  To make the stacked supply chart:

                   

                  1. Add the pills

                  • Drag Quantity to the columns shelf,
                  • Price to the Rows Shelf, and
                  • Make-Model to the Colors button.

                   

                  2. Convert this to a bar chart.

                   

                  3. Add a running total quick table calc to Quantity, sort by price. Tableau 10s new table calc editor makes it possible to do this in one window. Previously the sort option was hidden in an advanced -- blissfully ignored because sort order isn't relevant to most table calculations. But it's always relevant to running total.

                   

                  After adding the table calculation, Kia should be the left-most bar and Land Rover should be the right-most.

                   

                   

                  4. Tableau 10 Bar Size Magic

                  • Drag quantity to the Size button. With this data set, there is only one row per make-model and, therefore, min, max, sum, and average all result in the same result. I've used AVG(Quantity) to make this more robust to cases where there may be multiple rows (for example, real sales data segmented by date sold).
                  • Right-click the Make-Model pill on the Marks shelf and select sort.
                    • Sort by average price ascending.
                  • Click on the Size button and select right alignment.

                   

                  5. That's it!  Add some labels and a couple of formatting tweaks:

                  2 of 2 people found this helpful