Ad-hoc calculations are new in Tableau 9.0. They’re quite useful. And they are fun! There’s so much you can do with them. They are indeed one of my favorite things.
Here’s a quick start from Tableau to get up to speed.
And, once you’re ready, let’s dive deep for some Tableau tips and tricks using ad-hoc calculations in Tableau 9:
Create Ad-Hoc Calcs by dragging text from the Calculation Editor into the view or onto shelves
Do the reverse: Drag and drop ad-hoc calcs into the calculation editor
Drag and drop the ad hoc calculation from the view to the Dimensions or Measures in the Data pane to actualize them as calculated fields in the data connection.
You knew you could create an ad-hoc by double clicking on Rows, Columns, or in the Marks card. But did you know you can create them on the Measure Values shelf too?
Create titles for views. Just double click a space on Columns type the title inside quotes and do some formatting. Why would you do this? Check out this post.
Drag and drop fields, sets, and parameters into ad hoc calcs. Just start the ad-hoc calc, then drag from the Data pane or from anywhere else in the view:
Double click any field in the view to start an ad hoc calculation. If the field is from the connection, the code starts with a reference to that field. If it was an ad-hoc calculation then you pick up editing where you left off.
Use them to sort a view, even with blended fields and calculations that don’t always play nice with sorting. Here I copy the field (hold Ctrl while dragging) I want to use for sorting from Columns to Rows, double click it to Edit it as an ad hoc calc, place a negative sign in front to sort the direction I want, change it to discrete and move it to the front so it defines the sort. You could do similar things with ad hoc calculations using the Rank() function.
Multi-Line Ad-Hoc Calcs. While typing an ad-hoc calc, press Shift+Enter to start a new line. Caution: the field will only appear with the first line showing, so this is a great way to confuse your co-workers. However, it can be used to make things clearer – see #10:
Named Ad Hoc Calculations. Using the mult-line approach works really well if you make the first line a comment (i.e. start with the double slash: // ). Why? Because then the ad-hoc calculation gets a name which will show in Row or Column headers. Both of the fields on Rows in the view below are ad-hoc calcs with the code
City + ", " + [State]
but the second one is mult-line with the code
//City and State
City + ", " + [State]
And now you know 10 things you never knew before about ad-hoc calculations in Tableau.
Or maybe you did know these. And maybe you know other tips and tricks I didn’t mention. I’d love to hear from you in the comments!
Want to understand how Tableau works and how you can use it like a master?
Joshua Milligan is a three-time Tableau Zen Master. His passion is training, mentoring, and helping people gain insights and make decisions based on their data through data visualization using Tableau. He is a principal consultant at Teknion Data Solutions, where he has served clients in numerous industries since 2004.
Joshua served as technical reviewer on several Tableau titles and is the author of the book Learning Tableau.