Last week during training Carl went through all the possible functions (there's a lot!) that can be used in calculated fields in Tableau and so, I have summarised 5 functions I found cool and look forward to implementing in my analysis.
Note: all examples are using the Tableau Sample - Superstore.
- SIGN() - returns the sign of a number; 1 if it positive, 0 if it is zero and -1 if it is negative. One really good use case is on colour to highlight positive and negative values instead of having to write an IF statement out, as demonstrated below. This use case was originally discovered by Anne Jackson!
2. ROUND() - rounds values to a set amount of decimal places. The first use case that comes to mind is most likely to round a value to X number of decimal places, instead of formatting in the default properties number format. However, a use case I found more interesting was to set the decimal to be a negative value which allows you to manipulate large numbers to the nearest hundred, thousand, million, and so on. This use case has been demonstrated below.
3. DATEADD - adds an increment (i.e. which date part you want to add to and how much of an interval you want to add) to the specified date and returns a new date. This one is a really neat function because overall Tableau works at the day level. This means writing in a calculation [date field]+1 will only add one day to your date field...what about adding one quarter, month or year? (note: Tableau even works at the day level if it's a datetime field).
Therefore, the the DATEADD() function can help you here by allowing you to specify which part of the date to add or minus from (yes, even this calc allows the ability to use negative values!)
4. IIF() - a shortcut to writing out an IF statement where the condition (test) is stated, followed by the outcome if the condition is met and the outcome if it is not. What's great is this function also allows you to specify an outcome for an unknown. This function is going to be a good time saver!
5. FIND() and MID() - for the last one I've listed two different functions because they go so well together when returning part of a string.
The FIND() function returns the position of a substring within a string. For example, below I demonstrate how to find the start position of a customers surname. I can do this because I know there is always a space between the first and last name, so I specify my substring to be a space in single speech marks, and specify the start location to be one character after the space.
Next I can use the MID() function, which returns the characters from the middle of text given a starting position, to return the actual surname, as demonstrated below.
There are many more cool functions to be used in Tableau - these were a few that stuck in my mind for me and so I hope this proves useful for others!