Troubleshoot your calculations by creating a Tableau troubleshooting view! Read the last post in this series to get an overview of the method.
Whether the calculation is already in a view, or you're writing a brand new calculation, creating a crosstab with the dimensions that define the scope of the calculation shows us the exact results without any hidden influence.
Duplicate the view as a crosstab
- Right-click the worksheet tab
- Select Duplicate
why: Scope is defined by non-aggregated dimensions in a view. Duplicating as a crosstab is a quick way to make a crosstab with the same scope as the original view.
Move fields around with purpose
- Drag all dimensions to the Rows shelf
- Consider reordering dimension on the Rows shelf
- Drag measures to Text on the Marks card or to the Measure Values card
- Drag Measure Names to the Columns shelf
why: Generally Tableau views help us compare values to find trends or outliers. For troubleshooting, comparisons are not important. Instead a simple list of values tends to be the most effective way to see everything; However there is no hard and fast rules here. If it does not make sense to you, play around with it.
Remove any unimpactful fields, including filters. No really, remove them!
- Right-click a field
- Select Remove
why: The less data is in the view, the easier it is to see everything. And if a field is completely removed, then it is completely ruled out.
Good candidates for removal are any aggregated fields (other than the calculation in question) and everything on the filters shelf. Aggregated fields will not affect the output of the calculation. Some filters may be appropriate to keep while troubleshooting (see below), but in general we want to remove filters. If the filter introduces another issue, save the second issue for another round of troubleshooting: it's easier to troubleshoot one issue at a time.
- Drag a dimension to the Rows shelf (preferably use non-calculated dimensions)
- Right-click a dimension value in the view, or ctrl-click multiple dimension values
- Select Keep Only or Exclude
why: Using this method to filter the troubleshooting view provides a visual means to verify how the view has been filtered. Depending on your view, a self-contained example will be different. For example, the running sum of monthly sales per year could be filtered down to one year with all 12 months. Or, a calculation that bins customers based on their sales could be filtered down to one customer in each bin.
Move the filters from the Filters shelf to the Rows shelf or Color if troubleshooting filters
- Drag a field from the Filters shelf to Color on the Marks card
why: Troubleshooting a field on the Filters shelf is more difficult because the filtered-out values are not visible. With the field on Color, all possible outputs are visible on the Color legend, and the output for each row can be determined from the color.
Ready for more? Check out steps to break apart the calculation. To see it all in action, check the Calculations Survival Guide session at Tableau Conference!