5 Replies Latest reply on Mar 31, 2016 2:01 PM by Brian Coggon

    How to make up for missing data in a window_sum/lookup

    Lee Forst

      The idea is I want a dual axis chart that shows for the past 12 months the monthly totals as bars and a R12 (moving) trend line.  What I have built so far seems to work just fine.  Except for the source of data does not have data points for each month for each store.


      In the attached workbook, there are two data sources; With Missing Data and With Missing Data Added.  Each have the same structure; Store, Period, and Amount. The sheets "With Missing Data" and "Missing Data Graph" point to the With Missing Data data source.  If you look at "Missing Data Graph" and select Location 1 from the quick filter, this location appears as it should.  If you look at the data on the With Missing Data sheet, you will see basically how I'm doing this.  But if you look for Location 2, the R12 Value is not correct.  I know the problem is the window_sum and lookup and how I'm implementing them.  If you compare this with "With All Data" and "With All Data Graph" you will see how it should look because the missing months for Location 2 have been added (these sheets use the data source With Missing Data Added).


      I'm not sure how to continue to use method I've come up with and it still work with missing data.  Perhaps a different approach is needed yielding the same results.  Keep in mind I want a smooth trend line and not one that simply represents the values for each month.  And keep mind the example I've provided is a very small subset of data just to show the example.  Adding the missing data in the real data set is going to be very difficult.


      Thank you in advance to anyone who has the time to help me.

        • 1. Re: How to make up for missing data in a window_sum/lookup

          This may be different from what you're asking but I've found info that could potentially help for showing two trend lines; one trend line for the overall data and one for a subset:


          It is possible to display two trends lines, one for all the data and one for just an specific subset of the data by creating two graphs, one for each trend line then merging them with a dual axis.


          A sample workbook demonstrating this with sheets 1-3 and the evolution of the steps is attached.


          (Sheet 1)


          1. Create the desired graph and duplicate it by duplicating one field in the columns.


          2. Create a set by right clicking on the field where it is desired to create the subset. In this case it is going to be the TOP 4 Sub-Categories. A detailed description on how to work with sets can be found in the following article:


          Creating a Set



          Use this set in color in one of the graphs.


          You will see the data will be grouped into the Blue (Out of the Set) and the Red (IN)


          The trick is to make the out color on the second graph White so it does not appear. Change the color of out to White.


          (Sheet 2)


          Finally to merge both graphs into one right click on the field in Columns and select dual axis and then sincronize the axis.


          It is possible to format the marks then, making then bigger or smaller to better visualize all of them.


          More information about the Dual Axis and synchronizing axes feature can be found in the following article:


          Dual Axes


          • 2. Re: How to make up for missing data in a window_sum/lookup
            Lee Forst

            Thanks Diego for the information.  While it doesn't help my exact problem, it is an interesting concept and could prove to be useful.

            • 3. Re: How to make up for missing data in a window_sum/lookup
              Brian Coggon

              Hi Lee,


              I think I have a solution and an understanding of how to deal with missing data in a window_sum/lookup.


              In the example you gave it appears you are using a moving calculation line.  I believe this is just a “simple” example and you are hoping to apply your custom R12 Value table calculation (or the concept of it) to other areas, rather than the table calculation pane provided by Tableau.


              IF you are trying to use a running average or a moving calculation and have missing data a way to deal with this is to understand the “period” you are evaluating and in the Tableau dimension pill there is an option to “Show missing values”.  This method uses the period to drive what is seen and not seen over the view.


              IF you are using this example as a proof of concept for window_sum/lookup.  Your needs are a little more complex and you will have to write some conditional statements WITH IN the lookup to compare and inject a value.  The “lookup” looks specifically at the data and the rows in the data so if you want to use this method you would have to prepare your data before it comes into Tableau. The reason your Location 1 example works is because you DO have data for both missing and added that covers the 12 month period.


              I have attached a twbx with my table calc modification that shows the data table and the viz that compares R12 Value to sum(value) with table calc.


              Hope this is helpful…


              Cheers brian

              1 of 1 people found this helpful
              • 4. Re: How to make up for missing data in a window_sum/lookup
                Lee Forst



                Thank you for the response and ideas.  I haven't upgraded to 9.3 yet so I was unable to open your example.  However, it ended up doing what I think your second option is, and that was to have the underlying source fill in the missing data.  Luckily, I was able to craft the necessary query in SQL Server to fill in the missing data with 0.00 values for the measures for all of the possible combinations of dimensions.  As a result, the solution ends up working.  What is funny (in a sad way) is after all that work, we decided the view we were after really didn't help the story.  Oh well.  Learned a lot along the way!  Again, then you for taking the time to help me.

                • 5. Re: How to make up for missing data in a window_sum/lookup
                  Brian Coggon

                  Hi Lee,


                  Thanks for the reply.  Its always "fun" learning what may or may not work, as part of the exploration process. Here is the twbx in 9.2 (don't forget you can upgrade at Get Tableau 9.3)