Skip navigation

After you are done troubleshooting your calculations, here are some tips to keep track of them

 

Visually spot calculated fields

Calculated fields will have a = before their normal data type icon. See Visual Cues and Icons in Tableau Desktop

 

Search for calculations

  1. Click the magnifying glass next to Dimensions
  2. Start typing in the name of the field

 

Remember to clear out the search term when you need to see all of your fields again.

 

Name test calculations with a !

This naming convention means all of your test calculations will be at the top of the list in the data pane. Plus you can search for ! to find any test calculations in the workbook later, either to give them their final name or delete them.

 

Use folders or naming conventions to group calculated fields

Folders allow lists of fields in the data pane to be collapsed. See Group fields in folders

 

Naming conventions make it easier to search for a group of fields using a keyword. For example, you may consider appending "(worksheet name)" to the end of every calculation used on a worksheet, or "(tooltip)" to every calculation used on a tooltip.

 

See all calculated fields in a data source

  1. Navigate to Analysis > Edit Calculated Field...
  2. The menu lists all calculations in that data source

 

Find blended calculations

When working with data blending, you may be used to finding fields from the secondary data source because they will have an orange checkmark. But this isn't true for blended calcuations, aka calculated fields created in the primary data source that reference fields in the secondary data source.

  1. Navigate to Data > {Secondary Data Source Name} > Close
  2. Click OK in the warning that pops up
  3. Make a note of any calculations in the view or primary data source that become invalid (These are the blended calculations)
  4. Click Undo

 

Quickly delete all unused calculations

If you have a workbook that's been around for a long time and undergone many changes, you might have a bunch of legacy calculations that are not being used anymore. We can take advantage of the fact that only calculations in use are copied over into a new data source when replacing data sources to delete all of the unused calculations at once. Or we can hide all unused fields, show hidden fields, and then manually delete hidden calculations, which let's you see what the calculations were.

 

Option 1: remove all unused calculations at once

  1. Create a new connection to the same data source
  2. Navigate to a worksheet
  3. Navigate to Data > Replace Data Source...
  4. Choose the correct data sources in the Replace Data Source dialog, and click OK

 

Option 2: manually review and delete unused calculations

  1. Click the down arrow next to Dimensions, and select Hide All Unused Fields
  2. Click the down arrow next to Dimensions, and select Show Hidden Fields
  3. In the data pane look for gray fields with the = icon
  4. Delete or unhide fields as desired

 

You can select ctrl+click multiple fields to highlight them all, and then you can group delete or unhide them by right-clicking on any highlighted field.

Add comments

Comments can be a great way to explain why certain decisions where made in a calculation. Any line starting with // creates a comment.

Thank you for reading through the Calculations Survival Guide! Hope to see you at Tableau Conference

Troubleshoot your calculations by creating a Tableau troubleshooting view! Read the first post in this series to get an overview of the method.

 

As you troubleshoot your calculation, it is important to verify that the values in the view are correct. Both to help us continuously focus in on the issue, and to build an understanding of what is actually happening.

 

Ask guiding questions

  1. Are these values correct?
  2. How are these values actually generated?
  3. How do I calculate my end-goal?

 

why: Anytime you feel stuck, ask these questions to will help expose missing information you still need to troubleshoot the calculation. As you move through your investigation, the answers to these questions should become more and more specific until they sound like math problems. Formulating answers as math problems makes it easier to translate what you want Tableau to do into calculation syntax.

 

For example, if you were troubleshooting a quick table calculation to find the percent of total poker players in each game in a tournament, you may answer #3 as: “The percent of total should be the players in an game out of the players in the tournament

 

But eventually you could add more information to your answer until it sounded like: “The percent of total should be the unique count of players in an event, which is 4 for the first event, divided by the sums of all unique player counts for all events in the tournament, which is 4+5+3+6+5+4+2=29.” Of course I am making up numbers here, but these would be numbers from your view.

 

Add totals and subtotals with Total using Sum

  1. Navigate to Analysis > Totals > {Select an option}
  2. Right-click the measure field in the view
  3. Select Total using > Sum

why: Tableau Desktop can do the math for you, but Automatic totals are not always the sum of the values in the view. See Grand totals and aggregations to learn more about Automatic totals

 

Add more decimal points

  1. Right-click the measure field in the view
  2. Select Format...
  3. In the right-hand Format pane, under Default open the Numbers dropdown menu
  4. Select Number (custom)
  5. Adjust to the number of desired decimal places

why: Sometimes values appear wrong due to rounding errors. For example, percentages formatted as normal numbers without decimals will appear as 0 or 1 due to rounding.

 

Reveal hidden data

  1. Navigate to Analysis > Reveal Hidden Data

why: There is no visual indicator that data has been hidden, but sometimes hidden data is the cause of the issue.

 

Show headers

  1. Right-click the dimension field in the view
  2. Check Show Header

why: Headers may have been turned off in the final view, but will help us track data when troubleshooting

 

Check footer summary information

  1. Check the number of marks, number of rows, number of columns, and/or the sum of all measures in the bottom bar

why: This is a quick way to get some additional information about the view

 

Check tooltip summary information

  1. Click a header in the view
  2. Check the number of items selected and/or the sum of all selected measures

why: This is a quick way to count marks in a partition in the view. Remember that this will count all marks, which is rows x columns.

 

Next time we will look more in-depth at special cases, like Automatic grand total. To see it all in action, check the Calculations Survival Guide session at Tableau Conference!

Troubleshoot your calculations by creating a Tableau troubleshooting view! Read the first post in this series to get an overview of the method.

 

Breaking a calculation into its valid sections makes it easier to evaluate whether each piece is returning incorrect values. Incorrect sections warrant further investigation while correct sections can be ignored.

 

Create new calculated fields for valid subsections of the calculation

Create new ad hoc calculation from scratch

  1. Double-click on a shelf
  2. Type or paste the formula

 

Create ad hoc calculation from existing calculation

  1. Highlight a section of a calculation that is valid on its own
  2. Drag the highlighted section to a shelf

 

Create new calculated field

  1. Click the down arrow next to Dimensions in the data pane
  2. Type or paste the formula

 

why: Investigating each section of a calculation means you can narrow your focus. If there are multiple levels of nested functions, consider starting with bigger chucks and working down to smaller sections.

 

Add Sections to the view, remove any expressions that are returning the correct values

  1. Drag a measure to Text on the Marks card, or drag multiple measures to the Measure Values card. See Building a Text Table with Multiple Measures for how to get the Measure Values card.
  2. Drag dimensions to the Rows shelf. You may also consider dragging a dimension to Color on the Marks card depending on your preference.
  3. If a section is returning expected values, then remove it from the view.

 

why: Correct sections are irrelevant to the investigation and can be safely ignored

 

Format the calculation for readability

  • Make a new line for each function or expression
  • Make functions and operators all caps
  • Indent nested functions (You can highlight a block of text and hit Tab)
  • Use comments (Any line beginning with // will be commented out)

 

BeforeAfter

 

why: Seeing the whole calculation without scrolling makes it easier to keep everything in mind. Formatting can also help identify valid sections that can be broken out, and functions or parenthesis that need to be closed.

 

Search Errors

  1. Search the exact text of the error message in Tableau's Knowledge Base

 

Search Tips

  • Remove personalized information from searches, such as field names
  • Wrap phrases in quotations marks to search that exact phrase
  • Can't find documentation on an error? Let us know! We're always working to improve our documentation.

 

why: Tableau will only show results for valid formulas, so errors must be fixed first.

 

Ready for more? Check out steps to Verify the Data. To see it all in action, check the Calculations Survival Guide session at Tableau Conference!

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

  1. Right-click the worksheet tab
  2. 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

  1. Drag all dimensions to the Rows shelf
  2. Consider reordering dimension on the Rows shelf
  3. Drag measures to Text on the Marks card or to the Measure Values card
  4. 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!

  1. Right-click a field
  2. 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.

 

Filter the view to one self-contained example that still demonstrates the issue

  1. Drag a dimension to the Rows shelf (preferably use non-calculated dimensions)
  2. Right-click a dimension value in the view, or ctrl-click multiple dimension values
  3. 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

  1. 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!

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:

before.png

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.

after.png

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:

Picture1.png

 

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:

Build the View

Break Apart the Calculation

Verify the Data

Calculation Management Tips

 

Watch for more Calculations Survival Guide blog posts to learn more, and check out the Calculations Survival Guide session at Tableau Conference!

Hi, my name is Surya and I remain an addict.....of self-serve analytics solutions. I follow many self-service BI vendors very closely in the course of my job and influence enterprises in their technology buying decisions. Read my other posts here (https://www.linkedin.com/today/author/23200039) and here (www.ovum.com/authors/surya-mukherjee)

Growing up/evolving is hard work

Growing up is irritating, boring at times, and full of responsibilities. The need to consider all sides of an argument and not rush to change the world at every step, moving slowly (if required) in order to not break things, and developing empathy are all qualities that are valued in a mature organizational setting, but so difficult to inculcate for the most of us - me included.

But that's the reality of the large enterprise world. Things can be 'cool' and 'hip', but not at the cost of 'stable' and 'scalable'. Brevity and engagement, but not at the cost of clarity and functionality.

But enough with the long preamble.

Pop Quiz: What does Tableau do, other than visualization?

Chances are, if you have heard about Tableau, you are aware of its visualization capabilities, or the way it makes the analytics experience engaging for non-expert users. There is no denying that Tableau has been a poster child for self-service visual analytics, helping business users break free of draconian, IT-driven report factories. At the Tableau on Tour in London 2016 event, I met users whose careers have skyrocketed because of Tableau – or more accurately, their ability to find problems and opportunities in their business area with Tableau, which made them skilled industry experts highly desired by employers all and sundry.

That's amazing. At this point, if you feel an undying urge to cheer or wooooo, please sit down and hold it in, just for a little while. Yes, that means you too, Raj and Kevin - I see you. (names are not real)

Source: Twitter (https://pbs.twimg.com/media/ClG8lqXWYAA8dr_.jpg:large)

Because while Tableau loves you, visualization is not all Tableau is about.

As analytics customer/user expectations evolve in line with the market, even very large enterprises have started considering and deploying Tableau – not as a departmental solution, but across the enterprise and with IT involvement. Large organizations that come for the visualization and user empowerment story, need scalability, multi-structured data management across multiple sources, and interoperability with diverse enterprise applications to stay. At the event, Francois Ajenstat, vice president of product management, demonstrated that enterprise offerings and mature data management capabilities are very much a part of Tableau's story and vision. The vendor understands that visualization and self-service is no longer rogue or shadow IT, and a seat at the adult "enterprise" table comes with a certain set of expectations.

Read more here