Questions from Tableau Training: Conditional Scope on Tableau Calculations

Data

Questions from Tableau Training: Conditional Scope on Tableau Calculations

Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

Here’s a question that came out of a class from one of our other trainers:

“Student wants a running sum to restart every time the value drops to zero.”

How very interesting – a table calculation with a conditional scope. Let’s tackle this problem.

Scope & Direction

First, let’s do a quick review of scope and direction. Scope is how you partition the values in your chart for inclusion in the table calculation. Scope has three different values: table, pane and cell. Pane is only available when there are nested dimensions. In the case of multiple layers of nested dimensions, the scope of pane applies only to the last nested dimension.

In addition to scope, we also have direction. Direction is how the partition is addressed. Do we go down the scope or across the scope? Or in the case of undefined, do we consider all values in the scope. There are three basic types of direction: across, down and undefined. In certain situations, these directions can work together to zig-zag across the scope. This might be down and then across or across and then down.

There are advanced options with table calculations, such as choosing specific dimensions instead of scope and direction.

Running Sum

Let’s put this into practice with a simple table calculation. Let’s make up a simple data source in Excel to have our sales per day and then add a running sum to sales. Like so:

Simple data source

We are adding the daily sales values all the way down the table or in other words our table calculation is table (down).

Reset at Zero

Let’s modify our example and assume that we want to reset our calculation every time the daily sales hits zero. This is the running sum on days with consecutive sales. If it helps, it’d be the same as totaling the goals that Lionel Messi scored over his consecutive match scoring streak for instance.

Let’s solve it. I cannot use the running sum table calculation that Tableau would apply by default. If I drag that field over to the data pane and check out the new calculated field, I’d see this calculation:

Running Sum of Sales calculation

While I could build an IF/THEN clause to check for zero values, it wouldn’t reset the RUNNING_SUM function.

So instead, let’s build a custom table calculation in a calculated field:

Reset at Zero

The key here is resetting the running sum each time the IF/THEN logical operator encounters a zero. If it doesn’t, then it uses the PREVIOUS_VALUE function to carry forward the running sum to the current value. Let’s add this to our crosstab to see how it looks compared to the standard running sum table calculation:

New crosstab

Technically speaking, our scope and direction is still exactly the same. Within the logic of the calculated field, that’s where we apply our conditional usage of the running sum.

Great question! Let us know if you have any other ideas on how you can apply your table calculation conditionally in the comments section below.

Want More Questions from Tableau Training

  1. Questions from Tableau Training: Quick Filter Interaction
  2. Questions from Tableau Training: Labeling with Table Calculations
  3. Questions from Tableau Training: Speeding Up Presentation Views when Using Tableau
  4. Questions from Tableau Training: Dashboard Actions
  5. Questions from Tableau Training: Business Day Calculations
  6. Questions from Tableau Training: Adding Gaps Between Bars
  7. Questions from Tableau Training: When to Pivot Data in Tableau
  8. Questions from Tableau Training: Adding Conditional Formatting
  9. Questions from Tableau Training: Setting Weekday Start
  10. Questions from Tableau Training: How Do ZIP Codes Work in Tableau?
  11. Questions from Tableau Training: The Small Grey Bar Within a Quick Filter
  12. Questions from Tableau Training: Matching Mark Color
  13. Questions from Tableau Training: Showing the Null Indicator
  14. Questions from Tableau Training: Can I Manually Set the Colors of a Stepped Gradient?
  15. Questions from Tableau Training: How Can I Get Box Plot Data into a Crosstab?
  16. Questions from Tableau Training: How Do I Clear a Worksheet? Make a Copy?
  17. Questions from Tableau Training: Opening URL Actions Inside of a Dashboard
  18. Questions from Tableau Training: Can I Move Mark Labels?
  19. Questions from Tableau Training: How Can I Use My Current Report to Create a Viz?
  20. Questions from Tableau Training: Can I Create Text Hyperlinks on a Dashboard?
  21. Questions from Tableau Training: How Can I Transform My Crosstab into a Flat List?
  22. Questions from Tableau Training: Labeling the Top N
  23. Questions from Tableau Training: How Can I Customize Charts with Parameters?
  24. Questions from Tableau Training: Combining Measures into a Single Line
  25. Questions from Tableau Training: How Can I Create Custom Color Palettes?
  26. Questions from Tableau Training: Can a Top N Parameter be Nested Within Other Fields?
  27. Questions from Tableau Training: Conditional Scope on Tableau Calculations
  28. Questions from Tableau Training: Top Ten Plus N
  29. Questions from Tableau Training: Dynamic Label Positioning
  30. Questions from Tableau Training: Using Cross Joins to Solve Active Tickets
  31. Questions from Tableau Training: Top N Highlight Table
  32. Questions from Tableau Training: How to Use Specific Dimensions in Table Calculations
  33. Questions from Tableau Training: Moving Reference Line Labels
  34. Questions from Training: Comparing Metrics for Same Weekday in Previous Year
  35. Questions from Tableau Training: Colouring Specific Columns and Rows in a Crosstab
  36. Questions from Tableau Training: Creating a Custom Legend as a Sheet
  37. Questions from Tableau Training: How Can I Draw a 45-Degree Angle?

More About the Author

Robert Curtis

Analytics Consultant / ANZ Practice Lead
An Interactive Guide to Public Toilets in Australia Traveling requires planning. Where do you want to go? How will you get there? Where will you stay? What will you eat? These are the ...
Tableau Desktop Now Available in Dothraki With aims to broaden their global customer base, Tableau Software (NYSE: DATA) announced today a Dothraki language version. The ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK