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.


    Description:

     

    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]))

    END


    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]

    END


    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