KNOWLEDGE BASE

Add Additional Columns to A Crosstab when Every Row is a Measure


Published: 11 Oct 2016
Last Modified Date: 11 Oct 2016

Question

How to add additional columns to a crosstab where each column is a week and each row is a measure.

Environment

Tableau Desktop

Answer

See the attached packaged workbook "mega crosstab instructions" for an example. 
  1. Filter the view to show one additional week for every additional column needed. In the example, the view is filtered to show the past six weeks.
  2. Create a calculated field with a name like "Column Headers" and enter a calculation similar to the following:

    IF [Week of Order Date] > DATEADD( 'week', -4, DATETRUNC( 'week', TODAY() )) THEN STR( MONTH( [Week of Order Date] )) + "/" + STR( DAY( [Week of Order Date] )) + "/" + STR(YEAR([Order Date])) ELSEIF [Week of Order Date] = DATEADD( 'week', -4, DATETRUNC( 'week', TODAY() )) ELSE "Header 2" END 

  3. Drag [Column Headers] to the Columns shelf after WEEK(Order Date) Right-click WEEK(Order Date) and uncheck Show Header Right-click on "Column Headers" in the view and select Hide Field Labels for Columns Remove all measures from the view
  4. Add [Sub-Category] to the Rows shelf
  5. Create a calculated field with a name like "Row Headers " with a calculation similar to the following:

    IF [Sub-Category] = "Accessories" THEN "Sales" ELSEIF [Sub-Category] = "Appliances" THEN "Profit" END

  6. Drag [Row Headers] to the Rows shelf, to the right of [Sub-Category].
  7. Right-click [Sub-Category] and uncheck Show Header.
  8. Right-click on "Row Headers" in the view and select Hide Field Labels for Rows.
  9. Drag a second copy of [Row Headers] to the Filters shelf.
  10. In the Filter dialog, uncheck Null and click OK.
  11. Create a calculated field with a name like "New Sales" with a calculation similar to the following:

    { FIXED [Week of Order Date], [Row Headers] : MIN( IF [Week of Order Date] > DATEADD( 'week', -4, DATETRUNC( 'week', TODAY() )) AND [Sub-Category] = "Accessories" THEN { EXCLUDE [Sub-Category], [Row Headers] : SUM( [Sales] ) } ELSE NULL END )}

  12. ​Create a calculated field with a name like "Header 1 Sales Measure" with a calculation similar to the following:

    IF MIN( [Sub-Category] ) = "Accessories" AND MIN( [Week of Order Date] ) = DATEADD( 'week', -4, DATETRUNC( 'week', TODAY() )) THEN SUM(({ FIXED : SUM( IF [Week of Order Date] = DATETRUNC( 'week', TODAY() ) THEN [Sales] END )} - { FIXED : SUM( IF [Week of Order Date] = DATEADD('week',-1, DATETRUNC('week', TODAY() )) THEN [Sales] END )} ) / { FIXED : SUM( IF [Week of Order Date] = DATEADD('week', -1, DATETRUNC('week', TODAY() )) THEN [Sales] END )}  ) END

  13. Create a calculated field with a name like "Header 2 Sales Measure" with a calculation similar to the following:

    IF MIN( [Sub-Category] ) = "Accessories" AND MIN( [Week of Order Date] ) = DATEADD('week', -5, DATETRUNC('week', TODAY() )) THEN SUM( { FIXED : SUM( IF [Week of Order Date] = DATETRUNC('week', TODAY()) THEN [Sales] END )} - { FIXED : SUM( IF [Week of Order Date] = DATEADD('week',-1, DATETRUNC('week', TODAY())) THEN [Sales] END )} ) END

  14. Repeat steps 11-13 for any additional measures. In the example, the steps are repeated for [Profit] by replacing [Sales] with [Profit], and replacing "Accessories" with "Appliances".
  15. ​Drag all calculated fields created in steps 11-13 to Text on the Marks card.
  16. Click on Text and then on the … button.
  17. In the Edit Label dialog, remove paragraph breaks between calculated fields so that all calculated fields are on the same line.

Additional Information

The view created by the above steps is complex, and may be difficult to maintain or change. This is because Tableau Desktop is designed to create views that take advantage of the data's underlying structure to help viewers understand data relationships at a glance. Although the view described in this article is possible to create, as a visual best practice it is best to add more information in additional rows rather than additional columns. 

Consider some of the following suggestions to create views that help your viewers quickly find trends and answer questions:
  • Add all measures to the Measure Values card and use color to group measures.
  • Add information that would be in additional columns to Tooltip
  • Create a Key Performance Index to quickly narrow in on problems
  • Use a parameter to switch between groups of measures

The attached "Alternatives.twbx" workbook demonstrates some of these solutions.



 
Did this article resolve the issue?