I recently wrote about how some people often have the urge to verify data in Data Visualizations, and some steps we take to keep governance, and help people trust the data.
Despite that – people (finance…) – are still going to want to check. And for those folks, there are other things we can do in Tableau that speed up the process of checking complex calculations. We create QA sheets as a way to internally test our calculations that also allow the inheritor of the dashboard to validate however they see fit.
Tableau lets us create pretty cool nested calculations, even from levels of detail that are not in the view (AKA LOD Calculations). We have written some incredibly complicated formulas that always deliver the right answer but are hard for someone to dissect, whether in Tableau, or, god forbid, Excel.
The first thing to do when troubleshooting a complex calculation is to break it apart into smaller pieces and test each piece along the way. This process can be complicated and confusing as fields and calculations will often have very similar names. Tableau puts an = sign next to any calculation but I like to go one step further.
I group all calculations using numbers as a naming convention – that makes it far easier for me (and others) to read and process the calcs.
For example, instead of naming three calculations like the below:
- SUM PROFIT
- SUM SALES
- PROFIT RATIO
I instead name them:
- (_001) SUM PROFIT
- (_002) SUM SALES
- (_003) PROFIT RATIO (_001)(_002)
When looking at what field is used in a view, I can quickly reference the number of the calculation (001), versus having to read it to make sure it is the correct one, as typically there will be many calcs with similar names.
The other benefit to this naming convention is if I decide that I need to make a change to calc (_002), for example, I can type that into the search bar and not only will (_002) come up, but so will (_003) as the right hand side of the naming convention shows the calcs that are nested inside.
After 15+ years building Data Visualizations, I know that it is far easier and faster to find all calcs relevant to, say, SUM SALES when I look it up by a number (002) versus reading fields.
I’ve put the Boulder Insight Naming Convention Guide for Calculated Fields below in the comments (in the second comment) for anyone who might be interested.
Do you have any tips or trick for writing and finding complex calculations in Tableau quickly and easily? Please share in the comments – would love to hear.
GOALS
- Simplicity of naming
- Easy troubleshooting, especially when embedding calcs
- Easy to teach/train staff
- Easy to show/find total # of calcs in a given data source
NAMING FORMAT
- (_00#) Name of Calc (_*Referenced Calc)
- (_00#.P) Name of Parameter
PARENTHESES + UNDERSCORE
- The Parentheses + Underscore is added when writing calc names into the calculation editor/dialogue box
- Putting the Underscore on the search bar in the top left of your data pane will quickly show you only calculations, which allows you to see the total # of calcs in a datasource
NUMBER INDEX
- The # is a unique identifier for each calc at the beginning – should be 3 digits (_001) (_021), OR (_201)
- Start with _001 going up
- If you delete a calc DO NOT fill it. Just keep going up with your numbering.
- This helps you find calcs in pills on your sheets for easy updating and troubleshooting
NAMING
- The names are descriptive of what the calc is doing.
- They are not standardized like the numbers, so there’s no rules to try and remember