7. Lookup Functions in Tableau

Overview

Lookup functions are used to extract values that are offset from a target column. In this lesson, we learn how to use lookup functions to compare quarter-on-quarter and month-on-month sales data.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

Lookup function in Tableau

- Obtains values using an offset number from a target cell
- The offset can be a hard-coded number, a parameter or a formula
- A common use case for the lookup function is comparing Year-on-Year performance

How to write a Lookup function

- Formula takes the following structure: LOOKUP(expression, offset)
- The expression is the target array of data that you want to lookup
- The offset is the distance (in cells) from the target array

Transcript

In most industries, sales tend to be naturally seasonal For example, retailers tend to make more money in Christmas than they do in Spring and the flu vaccine sells more in October than it does in May As a consequence, management will often want to compare at the same time periods in consecutive years when benchmarking performance or when preparing a budget.

To do this in Tableau, I'd like to use the lookup function The lookup function is slightly different to the VLOOKUP or HLookup you might be familiar with in Excel.

For Tableau, it returns a value in a target row based on an offset from the current row Let's see it in action with an example I've already created a new calculated field called previous sales where I am going to add my Lookup function.

So, I'll write Lookup and in my expression, I'll include sum of sales.

And then my offset will by -1 And then I'll close the bracket Then I'll press OK, and add previous sales to my sheet with a double click.

And as you can see, if I change the order, we have sales for each year and then sales for the previous year What's more when I expand year to say quarter I have sales for the previous quarter Although Lookups can be applied to instances where dates are not present, I tend to find that this application of the function is by far the most common As you can imagine, lookups can be applied in a myriad of different use cases.

But when we are switching between years, quarters and months it's nice to be able to adjust the size of the offset which I currently have hard coded as -1 And to do this, I am going to use a parameter.

A parameter is a dynamic value that can replace a constant value in a calculation.

So, let's create a parameter with a right-click called time difference And this parameter will be an integer I'll set the current value to 1 and I'll include a range of say 1 to 36 and then press OK In the dropdown, I'll hit show parameter control so that the user can adjust the offset Now I am going to edit my formula to include the parameter So, I'll replace 1 with my parameter which is called time difference.

This is found on the auto compete, so, I can press Tab and then press OK If I'd now like to adjust my offset to be say 4 quarters, I simply drag to 4.

And now my quarters are offset perfectly So Q1 2013 is compared to Q1 2012.

Say I want to do months, I can simply adjust my time difference to 12.

And now I'm comparing January to January, February to February etc.

Not only can we compare current sales with previous year sales we can also plot the difference.

And to do this is very easy.

I'm going to create another calculated field which will be called annual sales change and this will simply be the sum of sales minus previous sales.

Then I'll press OK.

And add this to my measure values.

And as you can see, if I fit width, the changes between 2013 and 2012 for each month.

For clients or managers that like to use numbers to compare various time periods the Lookup function is an excellent option

>
Formulas in Tableau
Formulas in Tableau Part 1

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial