How to QA Tough Calculated Fields (Without Pulling Your Hair Out)

It’s tempting to try and solve calculation problems on the fly in your visualizations. Just slap down few calculated fields, throw them in your viz, and it’ll all tie out perfectly, right? Ha. More than once I’ve found myself pounding out changes to row-level calcs while watching my rolled-up visualization flounder, and after an embarrassingly long time of trial and error, I realize I had a silly mistake in one calculation that goofed up all the rest. I want to help others avoid that frustrating (and expensive!) method of QA-ing complex calculations. 

The Golden Rule: Don't try and QA complex calculations in your views. It will double your time invested and triple your blood pressure.

Instead, create an easy QA structure in your workbook using a new worksheet. Here's how.

Step 1: Break down your data to the lowest level of detail.

Let’s break it back down to the basics with a new worksheet. Personally, I name those worksheets things like “QA - YoY Sales to Category” and color them purple (my favorite tab color) so I can easily find them among all my other tabs.

Build a text table by dragging Dimensions onto the rows shelf to get to the lowest level of detail in your data set.

This enables you to see what’s happening on the row level, and then snap up to higher levels of detail and see where things are breaking. If it’s not right on the row level, it won’t be right on the rolled up levels. So this is where we start!

Build out your columns with discrete measures from your calculation(s).

Start with the calculation giving you trouble and drag in every column that goes into it, in the order they appear in your calculation. This helps you visualize the math as it’s happening, just like your high school algebra teacher used to have you show your work. For example, if you’re calculating Year over Year sales growth, bring in the Sales: Last Year, Sales: This Year, and Sales YoY in that order. Now you can see what you’re working with.

Check your row-level math first.

Do your calculations do what you expect? Does Sales YoY seem to be proportionate to last year and this year’s sales? Adjust your calculations on the row-level until each row is airtight as you move from left to right.

Step 2: Check the other levels of detail.

Now roll up your level of detail, if possible.

Do your calculations still look right? If they’re row-level calcs, they should.

Next up: Level of Detail Calculations. Add any LODs you want to check into your columns.

Take them for a spin!

Make sure your LOD calculations are working properly by rolling your level of detail up and down, or adding a Subtotal to your view to check against. They should hold true when you roll up to the total for your dataset, and when you go back down to the row/product level. Adjust your calculations so that when you take away a key dimension to roll it up to a higher level of detail, your fields yield what you’d expect.

Most common issue: you are missing a key dimension to fix/include/exclude in the LOD. Don’t feel bad. They’re tricky.

See how below, I’ve made sure our furniture Category sales fixed LOD adds up to equal the subtotal of the furniture Sub-Categories? Do this for all your LODs.

Step 3: Make sure it’s pushing through appropriately to your views.

Check your worksheets and dashboards.

Once you have confidence your calculations are performing correctly, go check your view. See if they look good, make sure you’re using the right fields, and check to see if you missed any LOD calculations you might need to check.

Keep that QA sheet, or make more of them for other sections of calculations.

They’re helpful to refer to throughout the development process, or to use in troubleshooting questions later.

Step 4: Celebrate!

Now that you’re confident your math is working, you can play around in the visualization all you’d like and be worry-free about your numbers. Give yourself a pat on the back.

Good luck out there, Tableauteurs!

Breaking down your complex math with a clear process can take a lot of the headache out of QA and troubleshooting. Got any good tips of your own? Tweet us at @GoDataDrive and let us know!

Explore More