KNOWLEDGE BASE

## Lookup Table Calculation in Tableau Prep

Published: 23 May 2018

### Issue

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

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.