Ad-hoc calculations are calculations that you can create and update as you work with a field on a shelf in the view. Ad-hoc calculations are also known as type-in or in-line calculations.
Double-click on an existing field to start editing.
|You can also double-click on an empty shelf or on an empty part of a shelf to create a new calculation.
|Type to update the expression, or drag new fields into the expression from the Data pane or elsewhere in the view.
Press Enter, Tab, or click outside the expression to commit the expression, close the calculation, and update the view.
Press Ctrl+Enter (or Command+Enter on a Mac) to commit the expression and update the view without closing the calculation.
Ad-hoc calculations are supported on the Rows, Columns, Marks, and Measure Values shelves; they are not supported on the Filters or Pages shelves.
If you are working in Tableau Desktop, errors in ad-hoc calculations are underlined in red. Hover over the error to see a suggestion for resolving it.
Note: Ad-hoc calculations are not named and stored in the Data pane, but they are saved when you close the workbook. If you want to save an ad-hoc calculation for use in other workbook sheets, copy it to the Data pane. In Tableau Desktop (but not in the browser) you will be prompted to name the calculation. Once you name an ad-hoc calculation, it is the same as any calculation you created with the calculation editor, and is available on other sheets in the workbook. See Get Started with Calculations in Tableau
As you're typing an ad-hoc calculation, you can press Shift+Enter to start a new line. However, only the current line is ever visible in an ad-hoc calculation, so this can be confusing for anyone viewing or editing the calculation who has no way of knowing that it contains multiple lines.
The first line of a multi-line ad-hoc calculation can be a comment that serves as a title for the calculation. This is the only line that is visible on the shelf after the calculation is committed:
//City and State
[City] + ', ' + [State]
If Tableau determines that the expression you enter is a measure (that is, returns a number), it automatically adds an aggregation to the expression when you commit the expression. For example, if you type
DATEDIFF('day',[Ship Date],[Order Date]) in an ad-hoc calculation and then press Enter, what you will see is the following:
SUM(DATEDIFF('day',[Ship Date],[Order Date]))
If you use a field that is already an aggregated field (for example, SUM([Profit])) in an ad-hoc calculation, the result is an aggregate calculation. For example, when you commit an ad-hoc calculation
SUM([Profit])/SUM([Sales]), the result is:
For more information on aggregate calculations, see Aggregate Functions in Tableau(Link opens in a new window).
Typically you create ad-hoc calculations on-the-fly to do things like
- Test a hunch
- Try a what-if scenario
- Debug a complex calculation
Use the following keystrokes to manage ad-hoc calculations.
- Double-click in an existing field on the Rows, Columns, Marks, or Measure Values shelf to open it as an ad-hoc calculation.
- >Double-click elsewhere on any of these shelves to create a new ad-hoc calculation from scratch.
- Press Esc to cancel an ad-hoc calculation.
- Press Enter to commit an ad-hoc calculation, which updates the view and closes the ad-hoc calculation. Press Ctrl+Enter to commit the change and update the view without closing the ad-hoc calculation.
- If you are working in Tableau Desktop, and there is a field to the right of the current ad-hoc calculation on the same shelf, pressing Tab opens that field as an ad-hoc calculation. If there is no field to the right of the current ad-hoc calculation on the shelf, pressing Tab opens a new ad-hoc calculation. Shift+Tab has the same functionality, except it moves to the left.
- When you double-click a named field on a shelf to edit it, you are not changing the original named field.
In addition, the following rules govern the use of ad-hoc calculations.
- Only one ad-hoc calculation can be open at a time.
- If a field that has a geographic role or a fiscal year setting associated with it is added to an ad-hoc calculation, the ad-hoc calculation inherits that role or setting.
- The right-click context menu for an ad-hoc calculation supports the same options that would be available for any other field in the view of the same type, including the ability to change aggregation or format.
- Ad-hoc calculations are not available when you create groups, sets, hierarchies, or parameters.
- Ad-hoc calculations are valid for creating trend lines, forecasts, and reference lines, bands, and distributions.