4 Replies Latest reply on Mar 26, 2011 10:05 PM by Richard Leeke

    Running Total, missing data

    . Clement



      I am trying to create a Running Total accross table. As you can see on the attached graph, some days do not include data from the previous day which create a drop from one day to another.


      For example, in day 6, grey data is missing compare to day 5. Even if there is no grey transaction on day 6, I was expecting that a running total would repeat the info from previous day even if there is no transaction in the current day.


      Any adwise?


      Thank you, Clément

        • 1. Re: Running Total, missing data
          Joe Mako

          There are a few ways to resolve this, here are two options:


          - preprocess, and pad your data, so there is a row in your data for every combination of date, the the field on your Color shelf.

          - Use data blending to achieve the same thing, it may be easier in some cases, but it also may limit other abilities.

          • 2. Re: Running Total, missing data
            . Clement

            Hi Joe,

            Coming back to your proposal: I went to the first option, ie padding my data (actually, I never managed to find how handle the data blending).

            My concern is that I need to add a 0 value not only for every combination of the data of my color shelf, but also for each of the filter used in the in the graph. Otherwise, when the user adjusts the view applying quick filters, the issue happens again. In my case, quick filters represent:

            * 6000 days (each day from 2003 to 2020)

            * quick filter 1: 6 values

            * quick filter 2: 3 values

            * quick filter 3: 10 values

            * quick filter 4: 50 values

            Creating one 0 value for each possible combination ends up to a table of 54M records... ! It just screws up all the performance, creating a huge non normalized data set.

            I still don't get why a running total in tableau is not a true running total, in the sense that if a data exists in one day, it should be repeated the next day, even if there is no additional entries coming after.

            Thank you, Best regards.

            • 3. Re: Running Total, missing data
              Joe Mako

              How about lines instead of a bars?


              One line for each value in the dimension on your Color shelf, and then another line for the overall value.


              That way you will not need to pad your data, and I believe the visual representation of the data as a line instead of a bar will better for quantitative analysis.

              • 4. Re: Running Total, missing data
                Richard Leeke

                One trouble with using a line chart is that the line joins up the points which do exist, which has the effect of interpolating for the missing values - whereas actually the missing values represent "no change".


                You can get around that and produce a "stepped line" representation with a bit of custom SQL trickery, as I described in this thread the other day.