Tablular Thursdays are all about doing cool things with our tabular data. This week, we’re going to look at a little function in Tableau, which is really useful.
That function is the lookup function. Let’s say you have the below data. In the lookup minus 1 column, we’re taking the value from the row before and populating it in our cell. In the lookup plus 1 column, we’re taking the next lines value and populating it in the cell.
| Date | Weight | -1 Lookup | +1 Lookup |
| 01-05-2020 | 79.1 | NULL | 78.9 |
| 02-05-2020 | 78.9 | 79.1 | 79.0 |
| 03-05-2020 | 79.0 | 78.9 | 78.7 |
| 04-05-2020 | 78.7 | 78.7 | NULL |
Why is this useful? For loads of reasons! Here is an example. I have been on a bit of a weight loss journey this year & I had made a Tableau dashboard to help me track what was going on.
Originally, I had a single line on my weight chart, which was, on that day, this was my weight. But cross referencing how heavy I was the prior month to see how far I’d come was a manual effort & a bit tedious.
So, I used the lookup function & created a multi line chart. Here, the green line is the weight I measured on the corresponding day. The grey line is the weight that I was 30 days prior to that day. The gap between the two lines shows me the progress I have made in that 30 day period.
Visually, this is much stronger than the single line – it takes less effort to determine what the delta is between now and now-30 days.
The way to do this is simple. The lookup function below takes the value of the chart measure from 30 records ago. Note the minus, as per our example above, it looks up in the table, to the previous records to extract the data we need.
LOOKUP(attr([Chart Measure]), -30)
This is a super simple formula but it has such great potential to make our dashboards more user friendly and insightful.
