I’ve broken down several scenarios to make working with functions simpler. This includes functions that perform similar tasks but are used in different situations, along with why you would choose one over another and practical examples for context.
1.ZN vs IFNULL
ZN([Sales]) - Returns the expression if it is not null, otherwise returns zero. It is the quickest, simplest way to handle nulls for numeric fields that only need to be replaced with zero.
IFNULL([Sales],expression) - Can replace the null with any value wanted. Grants flexibility to replace with whatever and for working across different data types (not just numeric)
e.g.
- IFNULL([Sales],0) - if sales value is null then replace with zero number
- IFNULL([City],’London’) - if sales is null replace with a string London
Note: Replacing null values with 0 can be useful for row- or column-level calculations, as many calculations won’t work with nulls. However, use this carefully as introducing zeros can distort results (e.g. averages) and make incomplete data appear complete. In some cases, it’s better to keep nulls visible to reflect the true state of the data.
2.TODAY() vs NOW()
Key difference = level of detail
- Use TODAY() for daily reporting
- Use NOW() for real-time dashboards where timestamps are important e.g. stocks-related reporting.
3.DATENAME vs DATEPART
DATENAME extracts a date part as a string label (e.g. "March"), whereas DATEPART extracts it as a numeric value (e.g. 3).
In Tableau, DATEPART can sometimes appear as a month name due to formatting in the view, but the underlying value remains numeric.
These functions can work with both existing date fields in your data and manually entered date values, as long as they are written in a valid date format.
- DATENAME('month', [Order Date]) → ‘March’
- DATEPART('month', [Order Date]) → 3
- DATENAME('year', #1986-03-25#) → ‘1986’
- DATEPART('year', #1986-03-25#) → 1986
4.ROUND vs FLOOR / CEILING
ROUND([Number], n) - Rounds to the nearest value using standard rounding rules.
- ROUND([Number], 1) → rounds to 1 decimal place
- e.g. 47.53 → 47.5, 44.23 → 44.2
If you don’t specify n:
- ROUND([Number]) rounds to the nearest whole number (not 10)
- Example: 47.53 → 48
FLOOR([Number])
Always rounds down to the nearest integer.
- Example: 47.9 → 47
- Use case: When you don’t want to overstate values (e.g. counting people, inventory)
CEILING([Number])
Always rounds up to the nearest integer.
- Example: 47.1 → 48
- Use case: When you need to ensure enough capacity (e.g. 2.3 teachers → 3 required)
When to use each:
- ROUND → General reporting where standard rounding is acceptable
- FLOOR → Conservative estimates (avoid overstating)
- CEILING → Planning/requirements (avoid understating)
5.FINDNTH vs LEFT / RIGHT
FINDNTH([Customer Name], 's', 2) - Finds the position of the 2nd occurrence of the letter 's' within a field.
When to use: When the position of the text varies and you need to locate a specific occurrence dynamically.
LEFT([Customer Name], 2), RIGHT([Customer Name], 2)
Extracts a fixed number of characters from the start or end of a field.
When to use: When the structure is consistent and you know exactly how many characters to extract.
The key difference is:
- FINDNTH locates position based on content.
- LEFT / RIGHT extracts based on a fixed length.
It’s quite likely that they are both used together to locate/extract a specific character/s.
My Takeaway
Understanding the differences between similar functions is less about memorising syntax and more about choosing the right tool for the right scenario.
While many of these functions can appear interchangeable at first glance, the impact of selecting the correct one becomes clear when working with real data, whether that’s avoiding misleading results or building more flexible calculations.
As a general rule:
- Use simpler functions when the requirement is straightforward
- Use more flexible functions when your data or logic is more complex
- And always consider how your choice affects the final output
Try using these functions in your next project and see how they impact your results.