Inserting Breaks for Sparse Data in a Running Total

Version 5

    Business Case:

    Every week, your DB gets populated with the previous week's sales. If the previous week had no sales, no record gets logged. Your manager recently asked you to create a report on the Running Sum of Sales for each series of consecutive weeks

    10-14-2015 3-19-17 PM.png

    The problem:

    When you open Tableau and bring weeks onto the Columns Shelf and Sales onto the Row shelf, you are left with a viz that looks like this

    10-14-2015 2-52-59 PM.png

    Here, Tableau is "Filling" in the line for weeks that don't exist in your Dataset. So we need to force Tableau to pad things out.



    Tableau will not break this out on it's own, we have to force it to. On your [Date] field on the Columns Shelf, Right click and "Show Missing Values".

    This forces Tableau to Break the continuous lines, and show the Dates (Weeks) that don't exist in the Dataset.

    10-14-2015 3-01-12 PM.png

    Here is where it gets a little trickier. Once you change you SUM(Sales) to a Running Sum, Tableau undoes all your hard work....

    10-14-2015 3-02-50 PM.png

    It is doing this because there are "Blank" marks being created from the step above. Theses "Blank" marks are getting a value populated into them with the Running Sum Table Calc. If you look at Marks on the Bottom Left of Tableau, you will see that it has increased from where it was previously.

    To resolve this, we need to take advantage of these "Blank" marks Tableau has created.

    IF ISNULL(LOOKUP(SUM([Sales]), -1))

    THEN SUM([Sales])

    ELSE SUM([Sales]) + PREVIOUS_VALUE(SUM([Sales]))


    By using LOOKUP() we can determine when a new series of consecutive weeks is starting, since (LOOKUP(SUM([Sales]), -1)) will be a NULL value (The blank space Tableau is populating). If it is the first instance in a new series, show the Sum(Sales) for that week, Else Grab the Sum(Sales) and add the previous value for Sum Sales. (PREVIOUS_VALUE is the only iterative calc in Tableau). This calc will effectively restart every time there is a break in consecutive weeks, as determined by the first IF statement.

    10-14-2015 3-10-17 PM.png

    Now that we have that, we need to add a Label. But we only want to add it to the end of each line. Sounds simple enough, but you can't do this the way you think you can.

    10-14-2015 3-12-15 PM.png

    Here Tableau is seeing this series as one line, so adding the label to only Line End will cause you to only show one value. To get around this we once again have to take advantage of the blank spaces Tableau is generating.

    IF ISNULL(LOOKUP(SUM([Sales]), 1))

    THEN [adjRunningTotal]


    If at any point the next value is NULL, then we know that mark is that last week in the series. So if it is, then show the adjRunningTotal, else show NULL.

    10-14-2015 3-16-42 PM.png

    And that's it! I have also added another calc in the example workbook that shows the running count of weeks. It uses the same techniques described above.

    If you have suggestions on how to improve this workbook, please let me know!

    Tableau Version: 9.0, but this can be accomplished with earlier versions

    Original Author: Rody Zakovich