KNOWLEDGE BASE

Excluding Null Values from Running Total


Published: 21 Feb 2014
Last Modified Date: 21 Jun 2017

Question

How to create a running total that does not extend past the dates that have actually occurred.

Environment

Tableau Desktop 

Answer

  1. In Tableau Desktop, add the field you want a running sum for to the view. 
  2. Select the drop down arrow > Quick Table Calculation > Running Total to see the full extent of data in the dataset. 
  3. Select Analysis > Create Calculated Field to create a calculated field.
  4. In the Calculated Field dialog box that opens, type a name for the field.
  5. In the formula field, create a calculated field similar to the following and then click OK:
    IF NOT ISNULL(SUM([<Field>])) 
    THEN RUNNING_SUM(SUM([<Field>]))
    END

    where <Field> is the field that is used in the running total quick table calculation.
  6. Create similar calculations for all measures in the view that are being calculated as running totals.
  7. Replace the running total quick table calculations in the view with the newly created calculated fields.

Additional Information

Note: This solution will work if you have rows/dates in your data set that don't yet have numeric data. For example, a skeleton transaction table with future dates in the year listed, but empty sales data until transactions have occurred. See the attached sample workbook for an example. 

To see these steps in action, watch the video below. 
Note: Video is silent. 
Did this article resolve the issue?