Showing the formula behind a Tableau quick table calculation

Tableau gives super-fast access to many common analytical operations via its “quick table calculation” feature: running total, percent difference, moving average and so on.

Just rightclick the measure pill, select “Quick Table Calculation”, make your choice and often that’s all that’s needed.

Capture

But what if you want to customise them in ways beyond what a simple “edit table calculation” can do, or understand how to reuse their functions in your own, more complex, formulae?

Behind the scenes actually all “quick table calculations” can be written out manually as a Tableau formula. You can work out how to do that by studying Tableau’s table calculation functions. But if you’re feeling lazy or lost, perhaps you’d rather just see how Tableau created its quick table calculations behind the scenes directly.

To do this, you can use the “edit in shelf” feature present since Tableau version 9.

Once your measure is set up as a Quick Table Calculation (which then will show a triangle to the right of its name on the shelf), you can right click it and choose “Edit in Shelf”.

This will convert it (temporarily) from a “quick table calculation” to its underlying formula which you can then copy for reuse, or edit.

The code behind my quick-table-calc “% difference in sales” is for instance:

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

You could then take that snippet and reuse it in your own calculations.

If you know the basics of the table calculation functions, you can also adjust the parameters directly in the shelf.

For instance, in the above example, perhaps you want the percentage difference in sales from 12 datapoints ago, not just since the last period. If so, just replace all the -1s in the above with -12, so the lookup looks 12 places to the left of the current value.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s