KNOWLEDGE BASE

Conditionally Aggregating by a Dimension not in the View When Using Another Table Calculation Filter


Published: 22 May 2014
Last Modified Date: 30 Aug 2016

Question

How to conditionally aggregate by a dimension that is not present in the view when the view is also being filtered by a LOOKUP table calculation filter.

Environment

Tableau Deskop

Answer

Step 1: Set up the view.
  1. Open Tableau Desktop, connect to the Superstore sample data, and drag Region and Customer Segment from Dimensions to Rows.
  2. Select Analysis > Create Calculated Field to create a calculated field.
  3. In the Calculated Field dialog box that opens, enter a name for the field. In this example, the field is named Show Hide LOOKUP.
  4. In the formula field, create a calculated field similar to the following: LOOKUP( IF SUM(Sales) < 500000 THEN 'Hide' ELSE 'Show' END, 0 ) and click OK.
  5. Place Show Hide LOOKUP onto Filters, select Show, and click OK.
  6. Select Analysis > Create Calculated Field to create another calculated field.
  7. Enter a name for the field. In this example, the field is named Count LOOKUP.
  8. In the formula field, enter: IF [Show Hide LOOKUP] = 'Show' THEN 1 ELSE 0 END and click OK.
  9. Select Analysis > Create Calculated Field.
  10. Enter a name for the field. In this example, the field is named Window Sum of Sales.
  11. In the formula field, create a calculated field similar to the following: WINDOW_SUM(IF [Count Lookup] = 1 THEN SUM( [Sales] ) END) where [Sales] is the measure to be aggregated. Click OK.
  12. Place Window Sum of Sales in Measures onto Text.
     
Step 2: Configure addressing and partitioning so that the aggregated results repeat across all of the dimension members that will be hidden.
  1. Right-click Window Sum of Sales on Text and select Edit Table Calculation.
  2. Under Compute Using, select Advanced.
  3. In the Advanced dialog box, move Region and then Customer Segment from Partitioning to Addressing, and then click OK.
  4. Under At the level, select Customer Segment
  5. Under Restarting every, select Region. Click OK.
 
Step 3: Add a Running Sum field to the view and then configure the addressing and partitioning.
  1. Select Analysis > Create Calculated Field.
  2. Enter a name for the field. In this example, the field is named Count Lookup Filter.
  3. In the formula field, create a calculated field similar to the following: RUNNING_SUM( [Count Lookup] ) and click OK.
  4. Drag Count Lookup Filter from Measures to Rows.
  5. Right-click Count Lookup Filter on Rows and select Discrete.
  6. Right-click Count Lookup Filter on Rows and select Edit Table Calculation.
  7. Under Compute Using, select Advanced.
  8. In the Advanced dialog box, move Region and then Customer Segment from Partitioning to Addressing, and then click OK.
  9. Under At the level, select Customer Segment.
  10. Under Restarting every, select Region. Click OK.
     
Step 4: Finish making the view.
  1. Drag Customer Segment from Rows to Detail to hide the field from the view.
  2. Drag Count Lookup Filter from Rows to Filters.
  3. In the Filter dialog box, select the check box next to 1, and then click OK.
Did this article resolve the issue?