Tableau lookup with a condition

1. Overview

In this article, you can go through the Tableau lookup with a condition.

In Tableau, you can combine LOOKUP function with a single or multiple conditions to create more complex calculations. This allows you to retrieve values from other rows based on specific criteria, making it powerful for time series analysis, comparisons, and other data manipulations.

The LOOKUP function in Tableau is used to reference data from a different row in your dataset, relative to the current row. This makes it a powerful tool for performing calculations that involve comparing values across different time spans or rows, such as month-over-month growth, year-over-year comparisons, or calculating running totals.

For all these examples, we will use the Tableau’s sample superstore data set.

1.1. How the LOOKUP Function Works:

The syntax of the LOOKUP functions is:

LOOKUP(expression, [offset])

expression: The measure or calculation you want to retrieve.
offset: The relative position of the row you want to reference.
0 returns the current row.
1 returns the next row.
-1 returns the previous row.

2. Tableau LOOKUP with a Condition

To apply a condition within a Tableau LOOKUP function, you typically combine it with IF statements, logical operators, or other functions like IIF or CASE.

Here are some examples:

2.1. Conditional LOOKUP Based on a Specific Value

Suppose you want to compare the sales of the current row with the previous row, but only if the current row’s sales exceed a certain threshold.

Calculation:

IF SUM([Sales]) > 100 THEN
    SUM([Sales]) - LOOKUP(SUM([Sales]), -1)
END

In this example:

• The condition checks if the current row’s Sales is greater than 100.
• If the condition is true, it calculates the difference between the current row’s Sales and the previous row’s Sales.
• If the condition is false, the result is NULL.

In the above view, the sum of sales for March 2014 is 1706. The lookup value specified in April 2014 takes the sum of sales in April and subtracts the sum of sales in March 2014 i.e., 308 – 1706 = -1,398.

2.2: Using LOOKUP with IF for Custom Flags

You can create a custom value that identifies whether the sales in the current row are higher than the sales in the previous row.

IF SUM([Sales]) > LOOKUP(SUM([Sales]), -1) THEN
    "PROFIT"
ELSE
    "LOSS"
END

In this example:

• The LOOKUP function retrieves the previous row’s sales.
• The condition checks if the current row’s sales are greater than the previous row’s sales.
• It returns “Profit” if true and “Loss” if false.

2.3. Tableau LOOKUP with a Condition Different Dimensions

You can compare values across different categories or dimensions using LOOKUP. For example, comparing the sales of the same product in the current year versus the previous year.

IF MAX(YEAR([Order Date])) = 2014 THEN
    SUM([Sales]) - LOOKUP(SUM([Sales]), -1)
END

In this example:

• The LOOKUP function is used to retrieve sales from the previous row.
• The condition checks if the order date is from 2014, and if so, calculates the difference with the previous row.

In the below view, lookup data appears only for 2014 and not for 2015 year.

3. Important Considerations:

• Partitioning and Addressing: The way your data is partitioned and addressed will affect the LOOKUP function’s results. Ensure to configure Tableau’s “Compute Using” settings to get the desired behaviour.
• Null Handling: If the LOOKUP function returns a null (i.e., when there’s no previous row), consider using the ZN function to replace nulls with zeros.

By combining LOOKUP with conditions, you can create dynamic and insightful visualizations that respond to specific data patterns or criteria.

3. Conclusion

As described in this article, you can combine the Tableau lookup with a condition like IF to create complex visualisations.

You can find code samples of our articles in our GitHub repository. To learn more about Tableau, refer to these articles.