3 Replies Latest reply on Feb 19, 2013 2:08 AM by Jim Wahl

    Difference in sales data

    P K

      i have 2011 & 2012 sales data . i need to develop  if 2012 sales data > 2011 sales data then Green color values else red color values.


      How to implement this in tableau


      Thanks PK

        • 1. Re: Difference in sales data
          Jim Wahl

          You can do this with a slightly modified quick table calculation.

          Sheet 1.png


          See the attached workbook or follow the below steps:

          1. Drag Product Type and Market to Rows shelf.

          2. Double-click Sales to put this on the Text shelf.

          3. Double-click Date to put years on the columns shelf.


          4. Right-click on SUM(Sales) pill on Text shelf > Quick Table Calculation > Difference -- by default this calculates the difference between the year columns.

          5. Right-click on SUM(Sales) > Edit Table Calculation > Customize. Now you see the basic table calculation (it's sometimes helpful to start from a Tableau-generated table calc).

          6. Add the >=0 to the table calculation. Name it Sales (2011 > 2010?)

               ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1) >= 0    ///See note


          7. Move this pill from the text shelf to the color shelf.

          8. Double-click again on Sales to add this to the Text shelf.


          9. On the legend, select edit colors.


          10. If you want to only show 2011 numbers, you can select the 2010 column and select hide. I also hid the headers


          NOTE: In the attached I used >= 1000 to make the data set interesting --- otherwise all values were positive and green.

          • 2. Re: Difference in sales data
            P K

            Thanks for the reply.. I have multiple measures like Quantity, Sales, Margin on Columns.. i need to highlight colors only on Margin.


            when i apply the above process for margin, colors applying to all measures. how to avoid that?

            • 3. Re: Difference in sales data
              Jim Wahl

              I believe Tableau v8 solves this problem, but in v7 your options are limited. The two I've seen are

              1 -- Make two worksheets (one with sales conditionally formatted; the second with the other measures and no formatting). Put these on a dashboard side-by-side and hide the row headers on the second sheet.

              Dashboard 1.png



              2 -- Multiple Axis Crosstab -- Jonathan Drummey's method here:



              Dashboard 2.png


              I think (2) works better, but it takes several more steps. Here's how I did it with the example workbook.


              1. Create two "dummy" fields.

              Z - 0.0 = 0.0

              Z - Fork Lift = 0.0

              The first allows us to put any measure in a column using the Label shelf. The second uses the dual-axis feature to add a label for the column header.


              2. Create columns.

              Move a set of these dummy fields (Z - 0.0 and Z - Fork Lift) to the Columns shelf for each measure you want a column for (I did this three times to create six pills on the Cols shelf). Right-click on the each of the Z - Fork Lift pills, select Dual Axis.  Adjust column width.


              3. Formatting. Remove zero lines.

              Select all six column pills (CTRL-click) > Format > Select the "Pen" icon > Sheet > Zero Lines (None). Edit axes.  Right-click on each of the bottom axes > Edit Axis (remove title) and Tick Mark (none). Right-click on each of the top axes, add a title (Sales, Profit, Margin) and remove the tick marks.


              4. Add Dimensions

              Add Product Type, Market to the Rows shelf. Add Year to the columns shelf (first position -- we'll hide the 2010 year below).


              5. Add Measure Values.

              On Marks shelf, remove any pills (for example Measure Values from the colors) and select multiple mark types. For <All>, select Text.  Click on Marks ">" to edit SUM(Z - 0.0). Place Sales on the Label shelf and Sales (2011 > 2010?) on the color shelf. Click Marks ">" SUM(Z - Fork Lift and select > Color > Transparency 0%.


              Repeat for the remaining measures -- Click ">" on Marks, add measure, click ">" change color transparency to 0%, ...


              6. Cleanup

              - Hide 2010 (right click on header label, select hide).

              - Hide field labels for columns (right click on Date).

              - Hide field labels for rows (right click on Market).


              7. More cleanup

              You can right-align the text for the non-colored columns by:

              - Changing the mark type for these columns to Gantt Bar.

              - Change color transparency to 0%.


              8. Option 2b --  to get the sales numbers right-aligned

              - Shade the cell.

              - Create another calculated field (Z - Size = 1)

              - Change the Sales column SUM(Z - 0.0) to type Gantt and transparency 0%.

              - Change top axis for this column to be 0 to 1.

              - Change Sales column fork lift type to be Gantt bar and put Sales (2011 > 2020) on color shelf and the new size field (Z - size) on Size shelf.