Just a few clicks and your calculation is ready, without opening the dialog window. Often without a title, and sometimes with the title that you cannot get in any other way. Ad-hoc calculations: when they can be a blessing and when a curse?
What are ad-hoc calculations?
There are at least seven or eight ways to write a formula for the calculation field in Tableau. And from those, one stands out in particular. Most will lead to creating a field that will be listed in the Data Pane section. Such fields have the names that can be later used as headers in the tables or charts. In contrast, an ad-hoc calculation will never be listed on the left side of the screen. You can create it by double-clicking on the Rows, Columns or Marks shelf. When it comes to their titles… there are different approaches to choose from.
For what can you use ad-hoc calculations?
Ad-hoc calculations can be helpful when you need to create something in Tableau on the spot. There is no need to open the dialogue window of the calculation field, and your ideas how too modify the visualization can be implemented straight away. There are also more sophisticated ways in which ad-hoc calculations can be used.
Pill “duplicate”
From time to time, you may need to format one field in a variety of ways in your visualization. For example, when you have thousands of labels with accurate values in tooltips. As a rule, Tableau does not allow it, but you can solve this problem if you have two copies of the same pill. This will create another issue – disorder in the Data Pane section. Using an ad-hoc calculation will help avoid this. For example, you can place an ad hoc [Sales]*1 calculation on the Marks shelf in the Tooltip tab. The value will remain unchanged, and Tableau will identify this element as a new field. This way you can have separate formatting for [Sales] and [Sales]*1.
Add headers on request
Charts or tables often have headers that are not suitable for whatever reason. For instance, the naming convention assumes that every field relating to the parameter should include PAR in its name. This makes understanding the report easier for other users, who will need to modify it. However, the name “QUARTERLY SALES PAR” isn’t what you want other users to see. In this case, you can modify the pill “QUATERLY SALES PAR” by using the ad-hoc calculation. There are two ways to do that.
In the ad-hoc calculation pill, you can enter the required name before the name of the relevant pill after two // characters or inside the symbols of the block comment – /* */. In the first case, after entering the required name press Shift and Enter before entering a formula. This will move your formula to the next line. There is no need to do this if you use block comments. When you apply this to your report, it may look like this:
Are ad-hoc calculations always a good idea?
When working with ad-hoc calculations, you should be aware that they have several considerable drawbacks. This means that you need to use them consciously. Why?
- They are not visible in the Data Pane section
When you come across an unknown field in the visualization, you usually check the Data Pane section. To learn more about this field, use Describe or open the Calculation Editor. But when these options fail, some Tableau users can wonder what to do. Even if you realize that it’s an ad-hoc calculation, but you encounter syntax which contains //, you may still be confused. Take a look at the above illustration – with this syntax, you should know that clicking through it will not suffice. You need to move the arrows up and down to unhide the remaining calculation using both Shift and Enter. Syntax with block comments will definitely have the edge.
- Not available in functions
Those who like to use a list of fields in the suggestions for calculations, may not find ad-hoc calculations there. You cannot access them either through the action filter fields, Replace References, or in any other lists available in the field report. They exist only locally on a particular shelf. This can hurt. Changing the filtering field to the ad hoc calculation can turn the filtering action off. By using Replace References, you can swap the selected fields out in all calculations and on all shelves of the report. All, except for the ad-hoc calculations.
What to do? Give up using ad-hoc calculations? Not necessarily.
They can be very helpful when you are building a report and reviewing the data. They enable flexibility and speed. However, when the report goes into the production phase, it is best to switch to the traditional calculations. These approaches should be applied only occasionally when creating reports. They can be used to resolve a bottleneck with the header. In such a case, it’s better to opt for block comments instead of two //. Below you can see how to swap out the ad-hoc calculation for the ordinary calculation.
Agata Mężyńska, Tableau Desktop Certified Professional
