LOOKUP in Tableau: the SQL equivalent, with a worked example

LOOKUP(SUM([Revenue]), -1)

Compute using: addressing Period; partitioning Region

Region	Period	Revenue	Cost
Harbor	1	120	70
Harbor	2	180	100
Harbor	3	180	120
Harbor	4	240	140
Upland	1	90	50
Upland	2	150	110
Upland	3	null	90
Upland	4	330	190

SELECT CAST((lag("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"

Runs the example above.

SELECT CAST((lag("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"

Written for this dialect; not run here.

SELECT CAST((lag(`Revenue`,1) OVER (PARTITION BY `Region` ORDER BY `Period`)) AS FLOAT64) AS result FROM input ORDER BY `Region`, `Period`

Written for this dialect; not run here.

Region	Period	Revenue	Cost	result
Harbor	1	120	70	null
Harbor	2	180	100	120
Harbor	3	180	120	180
Harbor	4	240	140	180
Upland	1	90	50	null
Upland	2	150	110	90
Upland	3	null	90	150
Upland	4	330	190	null

Same rows from the calculation and from the SQL in DuckDB.

Where a naive translation goes wrong

Omitting the region partition allows a calculation to consume marks from other regions.
Naive SQL
```
SELECT CAST((lag("Revenue",1) OVER ( ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"
```
Region Period Revenue Cost result
Harbor 1 120 70 null
Harbor 2 180 100 90
Harbor 3 180 120 150
Harbor 4 240 140 null
Upland 1 90 50 120
Upland 2 150 110 180
Upland 3 null 90 180
Upland 4 330 190 240
Matches the calculation
```
SELECT CAST((lag("Revenue",1) OVER (PARTITION BY "Region" ORDER BY "Period")) AS DOUBLE) AS result FROM input ORDER BY "Region", "Period"
```
Region Period Revenue Cost result
Harbor 1 120 70 null
Harbor 2 180 100 120
Harbor 3 180 120 180
Harbor 4 240 140 180
Upland 1 90 50 null
Upland 2 150 110 90
Upland 3 null 90 150
Upland 4 330 190 null

LOOKUP(expression, offset?)