How to Use Strings and Numbers in a Tableau Calculated Field

Alight Analytics Alight Insights, Tableau Best Practices


Hi, this is Sean McLerran with Alight Analytics. Today, I’m going to walk you through a few functions in Tableau’s calculated fields, how to embed both text and numbers into the calculated field, and how to format your field to present your data in the best way possible.

One of the most important functions in Tableau is being able to utilize calculated fields in order to present the desired data. It has been one of the hardest things to fully grasp and understand the capabilities of a data visualization tool like Tableau.

I’ll start out with a fairly easy IF calculation or statement to separate a subset of data.

Page Views for New Visitors: sum(IIF([User Type]=’New Visitor’, [Pageviews],null))/sum(IIF([User Type]=’New Visitor’, [Sessions],null))

Recently, I was asked to create a view that incorporated both text and numbers in order to present a logical sentence that could simply be read and interpreted. Tableau partnered with a refreshing data source allows the numbers in the field to update according to a parameter or date time chosen by the user of the dashboard.

Writing text into a calculated field is very simple, just make sure to place quotation (single or double) around the text. [Type: “New Visitors spent ” ]

The hard part is that now the rest of the field must be a string. To do this, simply put STR() around your integer fields. [Type: STR(INT([sessionDuration (New)]/60)) ]  The INT will only grab the number and leave out the decimals. Session Duration is measured in seconds as determined by Google Analytics, so we want to divide by 60 to get our minutes.

Next we want to grab the remainder of minutes for our seconds number.  [Type: “:” + STR([sessionDuration (New)] % 60) ] One problem with this is that a single digit amount of seconds won’t have a zero in front, so to better format we’ll use a right statement [Type: right(“0” + STR([sessionDuration (New)] % 60),2)  ] and take the right 2 digits.

Now we want more text (Type: + ” minutes per session and viewed ” +) We also want to add the average number of pages viewed by a new visitor. For this calc, we again need the STR to convert to string, [Type: + str(round([Pageviews per Session (New)],2))  ]

We can also format this field to turn it into a title in order to make the numbers stick out by enlarging their font. We want to make each calculation in the calc field its on measure (calc field). We can then add these individual calc fields in our title, enlarge their font, which will in turn make it easier for the user to read each metric.

And there it is, now we have a nice, easy to read calculated field that will change dynamically with time.

Thanks for listening, until next time.