One of the greatest features introduced in Tableau 9.0 was Ad-Hoc calculations. This feature allows you to add calculation formula directly to the rows, columns of marks shelf without creating calculated field beforehand. To learn more about Ad-hoc calculations, refer to Tableau Knowledge Base article here.
As this new capability allows you to save a lot of time, it can be confusing sometimes. To use it efficiently, you have to be aware of the drawbacks of this technique. Golden rule is: use it only for ad-hoc calculations (duh!) as for example Shapes can get out of control pretty fast.
Let’s use default World Indicators dataset to create very basic dashboard showing Tourism Balance per Year for selected Country. We will need 2 calculations:
first (Tourism Balance) to get balance of Tourism Value:
sum([Tourism Inbound]) - sum([Tourism Outbound])
Second (tourismBalanceIndicator) to assign shape indicating if value is positive or negative:
if [Tourist Balance] > 0 then 1 elseif [Tourist Balance] < 0 then -1 else 0 end
Ok, let’s create sheets 2 identical sheets – the only difference will be in marks shelf.
For the first one, let’s use ad-hoc calculation – the same used in the tourismBalanceIndicator. For the second let’s use calculated field itself:
Having Discrete (blue) pills on the marks shelf will help You manage colors and shapes easier as there will be only 3 values – 1, 0 and -1. Assign proper colors and shapes according to the values and move Sheets to the dashboard. You can add Country map with Country action to make it more interactive.
Right now, dashboard looks quite good:
Unfortunately, not for long :(. Changing anything that involves datasource – changing the datasource, publishing dashboard, etc. will cause values in ad-hoc calculations to be recalculated and therefore assigned default color and shape. See how this dashboard looks after publishing:
Using ad-hoc calculations instead of calculated field is one of the most common reasons for randomly changing shapes and colors. Help yourself and use ad-hoc calculations only in ad-hoc situations 🙂