3 Replies Latest reply on Jan 5, 2017 4:05 AM by michelle.smith.2

    Filling in Missing Months - Data Densification

    michelle.smith.2

      I'm trying to do a trend chart that shows a rolling 12 month sum of the current and previous 11 months over time.  I have a filter set to filter the view and not the data (lookup(min([Month]),0) >= [Start Month] AND lookup(min([Month]),0) <= [End Month]) and the calculation I have after following the article on data densification seems to work for months missing in the middle of the time frame I'm looking at.  However, is there a way to fill in the empty columns if there is no data at the end of beginning of the view?

       

      Example:

      I'm trying to show the rolling 12 months for company Bolten Cats and Tomcris dogs, and the view I'm interested in is the 12 months between November 2015 and October 2016.  The data point for November 15 is based on dates from 12/1/14 - 11/30/15, and for Oct. 2016 it is for data between 11/1/15 and 10/31/16.  The Lookup New filter filters the views correctly without filtering the underlying data.  I have a calculated field for sort state filed that shows the rolling amount for the last 12 months, which is used to sort the companies correctly.  I have analysis->Table Options-> Show Empty columns checked, and then Analysis->Stack Marks->ON.    I have one month missing for company Tomcris Dogs (July 2016), and the graphs seems to be correct as well as the label.  I have numerous months missing for company Bolten Cats, and the label seems to be correct; however, the graph ends at August 2016 instead of going to October 2016.  Any help to get the line extended to October 2016 and starting at November 2015 would be helpful!

       

      Data Source:

          

      RegionShowIncludeMonthCompanyAmount
      WestASYES28-Feb-14Bolten Cats$295,808
      WestASYES30-Apr-14Bolten Cats$170,751
      WestASYES30-Jun-14Bolten Cats$170,414
      WestASYES31-Jul-14Bolten Cats$206,896
      WestASYES31-Oct-14Bolten Cats$141,090
      WestASYES28-Feb-15Bolten Cats$226,765
      WestASYES30-Jun-15Bolten Cats$229,087
      WestASYES31-Aug-15Bolten Cats$77,469
      WestASYES29-Feb-16Bolten Cats$24,892
      WestASYES31-Mar-16Bolten Cats$48,679
      WestASYES31-Jul-16Bolten Cats$254,134
      WestASYES31-Aug-16Bolten Cats$273,905
      WestASYES28-Feb-14Tomcris Dogs$3,120
      WestASYES30-Apr-14Tomcris Dogs$4,712
      WestASYES30-Jun-14Tomcris Dogs$1,119
      WestASYES31-Jul-14Tomcris Dogs$2,916
      WestASYES31-Oct-14Tomcris Dogs$3,414
      WestASYES28-Feb-15Tomcris Dogs$2,926
      WestASYES30-Jun-15Tomcris Dogs$1,932
      WestASYES31-Aug-15Tomcris Dogs$1,670
      WestASYESNov-15Tomcris Dogs$1,192
      WestASYES12/31/2015Tomcris Dogs$1,169
      WestASYESJan-16Tomcris Dogs$3,274
      WestASYES29-Feb-16Tomcris Dogs$2,652
      WestASYES31-Mar-16Tomcris Dogs$2,235
      WestASYES4/30/2016Tomcris Dogs$1,865
      WestASYES5/31/2016Tomcris Dogs$2,736
      WestASYES6/30/2016Tomcris Dogs$3,269
      WestASYES8/31/2016Tomcris Dogs$2,870
      WestASYES9/30/2016Tomcris Dogs$3,202
      WestASYES10/31/2016Tomcris Dogs$3,970

       

       

      Thanks!

      Michelle

        • 1. Re: Filling in Missing Months - Data Densification
          Patrick A Van Der Hyde

          Michelle,

           

          The min/max value for the month/year in each pane are going to limit what can be done with the densification here.   What is the data source?  Is it possible to use some custom sql to add this data or to add rows for months for each customer and set them to zero?   Jonathan Drummey had an idea that gets to this here - https://community.tableau.com/ideas/1796  that you may want to vote on as well. 

           

          Patrick

          • 2. Re: Filling in Missing Months - Data Densification
            Jonathan Drummey

            Since I got pinged I'll jump in. My strongly held opinion for this kind of situation is that padding out the data in the Tableau data source is the only way to reliably get the right number of rows and be able to do everything else you might want to do in Tableau (joins, blends, filtering, filter actions, etc.). Here are some options to do that:

             

            1) Use Custom SQL as Patrick pointed out. This usually involves some sort of cross product & left join.

            2) Use a data prep tool such as Alteryx, Trifacta, etc. to pad out your data.

            3) Create a  "scaffold" source of every company & month (and maybe more dimensions) (potentially via Custom SQL) and then use that as the primary source and your raw data as the secondary source in a Tableau data blend.

            4) If you are on v10.0 then use a cross data source join: Create a scaffold source of every company & month (and maybe more dimensions) (potentially via Custom SQL) use that as the original table and left join in your raw data

            5) If you are on v10.1 then use a cross data source join: Create a scaffold source of every company & month (and maybe more dimensions) (potentially via Custom SQL) and right-join that to your raw data.

            6) v10.2 (currently in beta) will make it easier to build the scaffold source entirely in Tableau.

             

            Jonathan

            • 3. Re: Filling in Missing Months - Data Densification
              michelle.smith.2

              Thanks everyone.  I decided to create a data set that aggregates by every possible combination I need and then force a $0 if a month for that combination doesn't exist.  This seems to work, although I'm not in love with the solution!