Create Efficient Calculations
In cases where your data does not provide all the information needed to answer your questions, Tableau allows the creation of calculated fields to facilitate analysis. Calculated fields can contain a hardcoded constant, perform basic mathematical operations such as subtraction or multiplication, complex mathematical formulas, logical tests (such as IF/THEN and CASE statements), type conversions, and send expressions to external services.
Tableau offers different calculation types: basic and aggregate calculations, table calculations, and level of detail (LOD) expressions. Basic and aggregate calculations are part of the query to the data source and are calculated in the database. They scale well and can be optimized using database tuning techniques. Table calculations are calculated by Tableau on the query result set and are generally performed on a smaller set of records than the original data source. If table calculation performance is an issue, consider aggregating the data and performing the calculation on the aggregated data. LOD expressions are generated as part of the query to the underlying data source and are also calculated in the database. These expressions are expressed as nested selects, so they are dependent on database performance.
To improve performance, you might try replacing a LOD expression with a table calculation or a data blend. You can also use parameters to facilitate conditional calculations. Showing text labels but using underlying integer values for the calculation logic can make numerical calculations much faster than string calculations.
Converting date fields is a common task. Tableau offers the DATEPARSE function for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extract data sources. If the data does not support DATEPARSE, parse the field into an ISO string and pass it to the DATE function. For numeric fields, keep the data as numeric and use DATEADD and date literal values to perform the calculation for better performance.
When working with complex logic statements, CASE statements might be faster than IF or ELSEIF statements. Remember that ELSEIF is faster than ELSE IF, as a nested IF computes a second IF statement rather than being computed as part of the first.
Aggregating measures can significantly improve the performance of views, especially with disaggregated data. If the views are slow, select Analysis > Aggregate Measures to reduce the number of rows of data being viewed.
There are many little things that can be done to improve calculation performance. For example, using COUNTD aggregation sparingly, being cautious when using parameters with a wide scope of impact, avoiding filtering on complex calculations, and using NOW only if the time stamp level of detail is required. Finally, keep in mind that all basic calculations are passed through to the underlying data, so creating labels for large data sets should be done in a separate data source to avoid adding overhead to the main data source.
