KNOWLEDGE BASE

Computing a Table Calculation by a Dimension Not Present in the View


Published: 14 Apr 2017
Last Modified Date: 16 May 2018

Question

How to compute a table calculation by a level of detail that is not in the view. For example, creating a crosstab that shows the total of AVG(Sales) per [Category], [Segment] and [Region]. We then want to remove [Segment] from the view.
 

Environment

Tableau Desktop

Answer

Both options can be reviewed into the attached workbook "dimension not in view". 

Option 1: Use Level of Detail (LOD) expressions

  1. Create a calculated field with a name similar to "Avg of Sales Including Segment" and enter a formula similar to:
    { INCLUDE [Segment] : AVG( [Sales] ) }
  2. Replace [Sales] in the view with [Avg of Sales Including Segment].

Option 2: Add the dimension and hide it

Add all necessary dimensions to the view, and then clean the view up to look like extra dimensions had not been added. This method will only work for discrete views, like bar charts or crosstabs.
  1. Drag [Category] and [Segment] to the Rows shelf.
  2. Drag [Region] to the Columns shelf.
  3. Create a calculated field with a name like "Window Sum of Avg of Sales" with a calculation similar to the following:
    WINDOW_SUM( AVG( [Sales] ) )
  4. Drag [Window Sum of Avg of Sales] to Text on the marks card.
  5. Right-click [Window Sum of Avg of Sales] on the marks card and select Compute using > Pane (Down)
  6. Create a calculated field with a name like "First Filter" with a calculation similar to the following:
    FIRST() = 0
  7. Drag [First Filter] to the Filters shelf.
  8. Click OK to close the Filter dialog.
  9. Right-click on [First Filter] on the Filters shelf and select Compute using > Pane (Down).
    • Note: The table calculation setting for the [First Filter] will always be the same as the table calculation setting for the table calculation in the view.
  10. In the Filter dialog, check True and click OK.
  11. Right-click [Segment] on the Rows shelf and uncheck Show Header.
Please note, any additional measure added to this view will need to be wrapped in WINDOW_SUM() in order to show the value aggregated up to the [Category] level of detail.
 

Option 3: Sort by the dimension

Instead of partitioning a table calculation by a dimension, sort the table calculation by the dimension. Note: this method will allow for some table calculations to create continuous line graphs, but will also create some inaccurate results that must be filtered out.

CREATE THE CALCULATIONS
1. Create a calculated field with a name like "Rolling Average of Sales by Weekday" with a calculation similar to the following:

WINDOW_AVG(SUM([Sales]),-2,0)

In this example, we are calculating the rolling average of daily sales for the last 3 weekdays, e.g. the last three Mondays. This calculation will return the expected results as long as the view includes WEEKDAY(Order Date) and the table calculation is partitioned by WEEKDAY(Order Date).

The issue arises when attempting to create a line graph using this table calculation, because WEEKDAY(Order Date) will break up the line.

2. Create a calculated field with a name like "Weekday of Order Date" with a calculation similar to the following:

DATEPART('weekday', [Order Date])

3. Create a calculated field with a name like "First 2 Weeks Filter" with a calculation similar to the following:

MIN([Order Date]) >= DATEADD('week', 2, WINDOW_MIN(MIN([Order Date])))

NOTE: It is necessary to filter the first two weeks out of the view, because in the final view the [Rolling Average of Sales by Weekday] will be sorted by weekday rather than partitioned.
 
CREATE THE VIEW

1. Right-click and drag [Order Date] to the Columns shelf

2. In the Drop Field dialog, select DAY(Order Date) with the green calendar icon

3. Drag [Rolling Median] to the Rows shelf

4. Right-click [Rolling Average of Sales by Weekday] on the Rows shelf and select Edit Table Calculation…

5. In the Table Calculation dialog, do the following:
  • Select Specific Dimensions
  • Check Day of Order Date
  • For Sort Order, select Custom
  • In the Sort Order dropdown, select Weekday of Order Date, Minimum, Ascending
6. Drag [First 2 Weeks Filter] to the Filters shelf

7. In the Filter dialog, check True and click OK

Additional Information

By design table calculations only act on the data in the view.
Did this article resolve the issue?