• 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:

    1. Create Ad-Hoc Calcs by dragging text from the Calculation Editor into the view or onto shelves

     

    Drag and Drop from Calculation Editor

     

    1. Do the reverse: Drag and drop ad-hoc calcs into the calculation editor
    2. 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.
    3. 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?
    4. 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.

    Create Titles

     

    1. 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:

    Drag and Drop Into

    1. 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.
    1. 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.

    Sort with Ad Hoc Calculations in Tableau

    1. 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:

     

    1. 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] 

    Named Ad Hoc

     

    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!

18 Responsesso far.

  1. Wow! GREAT post Josh. I learnt some great stuff from this.
    Andy

    • Joshua Milligan says:

      Thank you very much for the kind words, Andy. There’s not a day that goes by that I don’t learn something new.

  2. Brad says:

    Josh, what a post. How did you find these?

    • Joshua Milligan says:

      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).

  3. Bora Beran says:

    Awesome post Josh! I am glad you’re finding ad-hoc calcs useful.

    • Joshua Milligan says:

      Thanks Bora! Definitely – LOD calcs get all the fame, but ad-hoc calcs are just there in the background, providing awesomeness throughout.

  4. Matt Lutton says:

    Great stuff — thanks for sharing Joshua!

  5. Kelly Martin says:

    Great work Josh – the gifs are very handy for me. It helps to see something repeatedly.

  6. Rajesh says:

    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!

  7. Mohit says:

    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

    • benmelek_admin says:

      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))

  8. manojreddy says:

    Hi Joshua Milligan.

    I want to learn calculated fields in depth logic will u help me what books or blogs i have to read.

    • benmelek_admin says:

      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!

Leave a Reply

Your email address will not be published. Required fields are marked *