Have you ever seen a cool tutorial that fails in your workbook? Do you need to update messy calculations in an inherited workbook? Do you look at valid syntax and just don't know what to change? Ever wanted to set your screen on fire?
Uncover the root cause of the issue by creating a troubleshooting view! Tableau is a tool for visualizing data, so why not visualize the problem with your calculation?
Check out this example:
I expected the [Top 10 Percentile] filter to show only the sub-categories in the top 10th percentile, but instead my view is completely unfiltered. I created the following crosstab to troubleshoot my calculation.
PERCENTILE([Sales],.9) is returning a different value for every sub-category, so I know the issue is with PERCENTILE([Sales],.9). If I look up the PERCENTILE() function, I find that PERCENTILE() is an aggregation just like SUM(). This means that the PERCENTILE() will be computed for every row in the view just like SUM(). I need to change my calculation to return one overall percentile value. See the solution on Tableau Public >
Unlike a normal view, a troubleshooting view is more about the process, letting us break apart and investigate each piece of a calculation. I like to break this process into 3 phases that we can cycle through:
Build the view
Build out a crosstab view that contains the calculation and all the dimensions used to define the scope of the calculation. As we troubleshoot, we can remove fields once we realize they aren't causing the issue or add new fields that help us investigate.
Break apart the calculation
Every calculation has valid sub-sections. By creating new calculated fields (or ad hoc calculations) with these valid sub-sections and adding them to our troubleshooting view, we can determine if the issue occurs for that sub-section. We are drilling down into the calculation to discover where the issue is.
Verify the data
While Tableau is good at math, it's still useful to bust out the calculator. This will help us A) figure out which parts of a calculation are incorrect, and B) what Tableau is actually doing to get the end result. Knowing where the wrong answer comes from helps us determine what needs to be changed.
What steps you actually need in each of these phases will differ based on the problem, but I have a list of steps I frequently take, which I have broken up by category:
Watch for more Calculations Survival Guide blog posts to learn more, and check out the Calculations Survival Guide session at Tableau Conference!