Earlier on in the week, I was shadowing an enablement session on Introduction to Calculated Fields in Tableau and someone asked a great question about two specific string functions: MAX and MIN.
Pause.
MAX and MIN on a string? That can’t be a thing. Max and Min would only work with numeric fields, right?
WRONG.
If we look in Tableau at the list of string functions…
There’s our MAX and MIN function!
According to Tableau’s documentation, MAX “returns the maximum of a and b (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, MAX finds the value that is highest in the sort sequence defined by the database for that column. It returns Null if either argument is Null.”
Similarly, MIN “returns the minimum of a and b (which must be of the same type). This function is usually used to compare numbers, but also works on strings. With strings, MIN finds the value that is lowest in the sort sequence. It returns Null if either argument is Null.”
Huh? What does that even mean?
If we look at the Sample - Superstore dataset and drop Region onto rows, we can see that the sort order goes Central, East, South, West:
If we clear the view, create a calculated field, and drop it onto text in the marks card, the results are as follows:
Based on data source order, the “highest” or in this case, last, in the sort sequence is the MAX of the string field.
What happens if we try to use MIN instead of MAX?
To try it out, I just duplicated the calculated field created and replaced the MAX in the calculation with MIN:
As expected, the “lowest” value is Central based on the data source order.
Though at the moment this explanation for MAX and MIN of a string function seems simple, it can get complicated when working with LODs which will be covered in another blog entry!
For more information on LODs in the meantime, please check out the existing blogs at https://thedataschool.co.uk/blog/?q=LOD or https://thedataschool.co.uk/blog/?q=Level+of+detail!