10 Things you didn’t know about Ad-Hoc Calculations in Tableau 9
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!
Wow! GREAT post Josh. I learnt some great stuff from this.
Thank you very much for the kind words, Andy. There’s not a day that goes by that I don’t learn something new.
Josh, what a post. How did you find these?
Thanks Brad! Some I discovered and some I remember seeing or reading somewhere (probably in alpha or beta notes — because I haven’t been able to find it since).
Awesome post Josh! I am glad you’re finding ad-hoc calcs useful.
Thanks Bora! Definitely – LOD calcs get all the fame, but ad-hoc calcs are just there in the background, providing awesomeness throughout.
Great stuff — thanks for sharing Joshua!
You’re welcome, Matt. Thanks for the comment!
Great work Josh – the gifs are very handy for me. It helps to see something repeatedly.
Awesome, Joshua! I was desperately searching for a solution to get the right sort order for a view. Your point 8 saved me tonnes of time and effort. Thanks for sharing!
A newbie in Tableau
I would like how , count, sum etc functions behave in Tableau if some the fields are null?
What’s the best way to resolve them
Tableau excludes Nulls from counts, averages, etc… If you do want to include them, you can always write a calculated field to convert them to a value first – e.g. COUNT(IFNULL([ID], 0))
Hi Joshua Milligan.
I want to learn calculated fields in depth logic will u help me what books or blogs i have to read.
Sure! I recommend checking out the resources on Tableau.com and using the forums for specific questions. Of course, I’d always recommend the book Learning Tableau! And I’d be happy to answer any specific questions you might have too!
Looking for a bit of help and thinking you are the right Zen to help me.
The data is for Bench marking metrics like Rent Janitorial Maintenance and Repair, as well as Landscaping. this is what i have done so far….IF [Metric]=”Janitorial” OR [Metric]=”Rent” OR [Metric]=”Landscaping”
ELSE [Repair & Maintenance]END
I cant seem to get the visual to change when selecting Rent or Landscaping. I had created a parameter called metric so i could use as a filter to select which one i wanted to show.
Any help would be greatly appreciated. Thank you!
Hi Shelly! It looks like switching between Rent and Landscaping would give the same result (Janitorial/Cleaning) in your code. However, switching to another value, such as “Repair” should give you a different value (i.e. Repair & Maintenance). Is this what you are seeing? Double check the spelling and case (upper vs. lower) of the parameter values compared to your code to make sure they are exactly the same. Hope that helps!