9 Replies Latest reply on Jul 26, 2018 10:32 AM by Steve Adams

    Creating a running sum with missing data

    Sam Bruce

      It appears from an older post ( Tableau 7.0 Extending Table Calculation) that this functionality has come and gone.  I am trying to plot the running total (ie year to date) of some product sales.  A particular product runs out of stock part way through the year but then reappears at the end of the year.  On the graph, the marks for that product disappear completely during the time of no sales because there are no records in the data.  The data table however shows the results I am expecting.  What's going on?  Is there a work around?  I've tried several formulas to convert the nulls to 0's but haven't had any luck.

      - running_sum(sum(ifnull([Dollars],0)))

      - running_sum(sum(zn([Dollars])))

        • 1. Re: Creating a running sum with missing data
          Pedro Machado

          Sam,

           

          I believe this has to do with how Tableau handles data densification, which varies depending on the mark type. Try using an area chart.

           

          This demonstrates the issue with fake data. See also attached.

           

           

          See this thread http://community.tableau.com/thread/129861

           

          Joe Mako may be able to elaborate on the technical reasons why densification is handled differently depending on the mark type.

           

          Pedro

          2 of 2 people found this helpful
          • 2. Re: Creating a running sum with missing data
            Joe Mako

            Thanks Pedro Machado

             

            There are a number of different types of Data Densification in Tableau, and some of them can happen at the same time, adding to the complexity.

             

            To use Data Densification awareness of the 4 Pill Types of Tableau is fundamental:

            - Discrete Dimension

            - Continuous Dimension

            - Discrete Measure

            - Continuous Measure

             

            For your situation, you want the following:

            - a Bar mark type

            - a Dimension pill on the Marks card

            - a Discrete Dimension on the Columns shelf (potentially a Date data type)

            - a Continuous Measure on the Rows shelf

             

            Any variation to this may have additional impact. This is why Data Densification is non-user friendly, there are too many exceptions and factors to list in a single comment here.

             

            The attached workbook offers a couple of potential routes to get the results you are looking for.

            - "Bin Show Missing" uses a bin on an number to ensure regular intervals from the smallest value to largest value at the bin interval. I call this method, "Show Missing Densification on a Range Aware Pill"

            - "Date Domain Complete" this uses a Discrete Dimension pill that is from a date data type field. When this pill is used as the only dimension for compute using, Tableau will perform "Domain Completion Densification". This means for every potential combination of dimension values that exist, we get a mark. This not a not a range aware densification, see next for what that means

            - "Filtered Date Domain Complete" this is the same densification method as the prior, but here we can see what happens when some dates to not exist in the data, since they are filtered out.

            - "Filtered Date Show Missing" is also a "Show Missing Densification on a Range Aware Pill", the date pill is range aware because of how it is configured, and the Show Missing Values option adds marks for those missing.

             

            The two options that I would recommend would be either the "Bin Show Missing" or the "Filtered Date Show Missing"

             

            This is NOT a complete list of all Data Densification routes. If you or anyone would like to discuss the details of data densification in Tableau, including why mark type impacts densification, you are welcome to email me, found in my profile, and we can meet for a screen sharing session.

            2 of 2 people found this helpful
            • 3. Re: Creating a running sum with missing data
              Sam Bruce

              Thanks Pedro & Joe.  I see how I can work around this to get the results I need.  I  like the Bin method as my intervals are not date type.  Hopefully on my data set this isn't too calculation expensive.

               

              Truthfully though I'm not sure why I need a work around.  I understand the handling of this type of data has flip-flopped over the last few versions and I leave it to the Tableau folks to land on the best solution.

               

              You win some and you lose some.

               

              Thanks,

              Sam

              • 4. Re: Re: Creating a running sum with missing data
                Pedro Machado

                Thanks, Joe!

                 

                Sam,

                 

                The bin calculation should not be very expensive. The calculation is handled by the database and only the aggregated results are returned to Tableau.

                 

                Here the SQL it generated for Joe's example:

                 

                SELECT Int([Sheet1$].[n] / Val(1)) AS [n (bin)],

                  [Sheet1$].[t] AS [none:t:nk],

                  SUM([Sheet1$].[v]) AS [sum:v:qk]

                FROM [Sheet1$]

                GROUP BY Int([Sheet1$].[n] / Val(1)),

                  [Sheet1$].[t]

                 

                Let us know how it goes.

                 

                Pedro

                • 5. Re: Creating a running sum with missing data
                  Paul Ausserer

                  Joe,

                  Any recommendations on how to fill missing rows that are prior to the first data mark?  In your attached workbook (Filtered Date Show Missing), if you deselect the first week the first column becomes week 2 but if you deselect week 2, week 2 remains on the visualization and starts with week 1. Ideally I need week 1 to always display. Any recommendation on how to fill missing values as defined by the axis range, not the first record of data?

                   

                  I worked around this issue by building a domain table with all possible values (including non-values = 0) but that resulted in over 80M rows, it would be great if Tableau could manage Nulls across a defined domain.

                  • 6. Re: Creating a running sum with missing data
                    Joe Mako

                    Paul,

                     

                    You are welcome to email me, found via my profile, and we can setup a time a to meet for a screen sharing session and would through your situation together.

                    • 7. Re: Creating a running sum with missing data
                      Steve Adams

                      Joe

                       

                      Can you believe this is still valid and required??

                       

                      Many, many thanks for this

                       

                      Steve

                      • 8. Re: Creating a running sum with missing data
                        Joe Mako

                        Steve,

                         

                        Yes, these are the fundamentals of Tableau, and I do not expect it to change anytime soon. There is a great deal of complexity in Tableau.

                        • 9. Re: Creating a running sum with missing data
                          Steve Adams

                          Hi Joe

                           

                          I enjoy finding these workarounds. However I was a little surprised that if I can “show missing values” that the running_sum table calc doesn’t give me an option to use them. But I’m probably missing something in the underlying mechanics.

                           

                          Once again, many thanks

                           

                          Steve

                           

                          ——

                          Steve Adams

                          Senior Consultant

                          Visual DJ Ltd

                          Tel:      (44)/(0) 7957 098 459

                           

                          www.VizDJ.com<http://www.VizDJ.com>

                          www.TableauTraining.co.uk<http://www.TableauTraining.co.uk>

                           

                          Disclaimer

                          This e-mail is confidential and may also be privileged. If you are not the intended recipient please notify the sender IMMEDIATELY and remove the e-mail from your system. You should not copy the e-mail or use it for any purpose or disclose its content to any other person. E-mails are susceptible to interference.  You should not assume that the contents originated from Steve Adams or Visual DJ Ltd or that they have been accurately reproduced from their original form.  Visual DJ Ltd accepts no responsibility for information, errors or omissions in this e-mail nor for its use or misuse nor for any act committed or omitted in connection with this communication.  If in doubt, please verify the authenticity of the contents with the sender.

                           

                          This Email was sent by Visual DJ Ltd. Registered Office: 332 Marsh Lane, Erdington, Birmingham, West Midlands, United Kingdom, B23 6HP

                          Registered in England No. 10616287