When you’re creating calculated fields in Tableau, there is nothing more frustrating than seeing the red squiggly line under your calculation.
‘This calculation contains errors’ Tableau will tell you. What errors? What’s gone wrong? How can I fix it?!?
Although this message may be infuriating, by clicking on it we can start to understand what has gone wrong in a calculation and by selecting the pop-up error message that appears, Tableau will even highlight the part of the expression which needs our attention. Handy!
In this blog post I am going to demystify some of the main errors which can easily happen when putting together expressions.
1. Cannot mix aggregate and non-aggregate values with this function
An aggregate is a mathematical operation which takes multiple values and combines them into one single value. These values are grouped together and summarised with functions such as Sum, average, count etc… This differs from non-aggregate data which is taken at the row level, the greatest level of granularity in the data. Tableau is unable to combine aggregated data and non-aggregated data in certain types of functions.
We need to modify this calculation so that all fields are either aggregate or non-aggregate, this will depend on what you are trying to do with your calculation.
In the case above, I want the sales per product for each product order. Each row of data in our data set represents a product order so I want to computer my calculation at the row level. Therefore, to solve the error above, I would need to remove the SUM() function from around Sales.
2. Expected closing parentheses or comma
This is a simple one and pretty self-explanatory, you’ve just forgotten to add a comma or a bracket somewhere.
Simply find the place where you’ve forgotten the comma or parentheses and add it in (in this case, after profit). Tableau’s red squiggly line can sometimes be helpful here to identify the part of the expression where a character is missing. This gets trickier when writing longer, more complicated expressions with more fields and functions.
3. Cannot add string and float values
The problem here is that our two fields that we are trying to add contain different data types (Customer Name is a string and Sales is a float) therefore Tableau cannot add them together.
If you are trying to add the contents of two fields, then you’ve made a mistake with one of the field names. However, if you wanted to create a field which concatenated an integer or float with a string, you would need to use a conversion function within your expression. Use the STR() function around the field that contains numbers in order to change the format to a string within the calculation.
4. Syntax Error (maybe you are missing an identifier/operator).
Tableau is erroring in this case because something is missing from the calculation. This could be an operator (the symbol which tells Tableau what to do), or this could be an identifier (a literal expression which tells Tableau how to identify something).
In the case above we have used an if statement to identify sales greater than 100 as ‘Over 100’. We have also told Tableau that we with to identify those that do not meet this criteria as something else, with our ELSE function. However, we have not told Tableau how we want to identify sales which do not meet this criteria.
In order to solve this error, we need to tell tableau how to identify these. If we add ‘100 or Under’ after ELSE then this error is resolved.