KNOWLEDGE BASE

Lookup Table Calculation in Tableau Prep


Published: 23 May 2018
Last Modified Date: 30 Dec 2022

Issue

Unable to leverage the LOOKUP table calculation in Tableau Prep to reference a different row's value for analysis.

Environment

Tableau Prep 

Resolution

Use a shifted self-join to find an offset value. The following example uses the sample data set Superstore to find the total sales for the previous day. Both the workbook and the Prep flow are downloadable from the attachments section of this article.
  1. Connect to Superstore in Tableau Prep. See Connect to Data for detailed instructions
  2. Create an aggregate step. See Aggregate and group values for detailed instructions
  3. In Aggregate 1, drag Order Date to Grouped Fields and Sales to Aggregated Fields
  4. Create a clean step and name it "Create Row Number"
  5. In the "Create Row Number" clean step, do the following:
    1. Create a calculated field named "Row Number" with a formula like 
    2. { ORDERBY [Order Date] ASC : ROW_NUMBER() }
    3. Rename [Sales] to "Sales Per Day"
  6. Create a clean step and name it "Row Number + 1" 
  7. In the "Row Number + 1" clean step, do the following:
    1. Create a calculated field named "Row Number + 1" with a formula like
    2. [Row Number] + 1
    3. Rename [Sales Per Day] to "Previous Sales Per Day"
    4. Remove [Row Number]
  8. Drag the "Row Number + 1" clean step over the "Create Row Number" clean step and drop on "Join"
  9. In "Join 1" do the following:
    1. Update the join clause to [Row Number] = [Row Number + 1]
    2. Click on the Venn Diagram to create a Left join 
  10. Create a new clean step off of the join and name it "Remove Fields"
  11. In the "Remove Fields" clean step, remove the fields [Row Number], [Row Number + 1], and [Order Date-1]
  12. Drag the "Remove Fields" clean step over the "Create Row Number" clean step and drop on "Join"
  13. In "Join 3", Tableau should automatically join on [Order Date] = [Order Date]
  14. Add a clean step after "Join 3" and name it "Merge Join Fields"
  15. In the "Merge Join Fields" clean step, merge [Order Date] and [Order Date-1]
  16. Output the data and connect to the output in Tableau Desktop
  17. Right-click [Previous Sales per Day] in the right-hand data pane and select Default Properties > Aggregation > Average
  18. Repeat step 17 for [Sales Per Day]

Cause

The ability to use a LOOKUP table calculation in Tableau Prep is not currently built into the product.

Additional Information

  • Sometimes table calculations in Tableau Desktop will be the better solution. Table calculations are dynamic: if [Category] is added to the Rows shelf in "Table Calculations Example" worksheet in the attached workbook, then the table calculation will automatically update to return the previous day's sale per category. Conversely, output from a prep flow is static. This means that the prep flow will have to updated to change the output.
  • The beginning aggregate step is necessary in this example because there are multiple records per day. If the goal is to return the value from the previous record in the underlying data then the aggregate step is not needed. Steps 17 & 18 will also be unnecessary.
  • Steps 17 & 18 to change the default aggregation to average are necessary because Tableau Prep returns the total sales value for every record on the same day. Thus SUM([Previous Sales per Day]) will be duplicated by the number of orders on that day. There are multiple ways to handle this duplication, for another solution see Removing Duplicate Data with LOD Calculations
  • The shifted self join could also be on something like Order Date = Order Date + 1. However, this will create NULLS if there are missing dates, which is why this solution joined on Row Number = Row Number + 1 instead. 
Did this article resolve the issue?