3 Replies Latest reply on Feb 7, 2019 8:52 AM by Joshua Milligan

    Pulling Data forward when none is available

    Chris O'Hollearn

      I am working with marketing research data over multiple years. Unfortunately, we don't buy all market research every year, so there are gaps in the data. The request is to pull ahead data in (for example) 2015 and fill in blanks on 2016. I'm pretty sure I would be able to accomplish this in excel, but I don't want to pull my data out of tableau prep, and put it back in once I've updated it. Below is a brief example of what I'm talking about.

        

      Source2015201620172018
      a23x27x
      bx34x40
      c25312334
      dxxx20
      e20xx23
      fxx4140
      g31x3329

       

      If I have data for any subsequent year, I would like it pulled ahead to fill in any blanks, but not replace data that is there. If no data is available in any of the previous years, then it can remain blank. In the example below for source a, I would fill in the 2016 number with 2015 number, and 2018 with the 2017 number. See below.

        

      Source2015201620172018
      a23232727

       

        Do you know how I would accomplish this, or what calculation I could write to do this?

       

      Any help you can provide would be much appreciated.

      Thanks,

      Chris

        • 1. Re: Pulling Data forward when none is available
          Joshua Milligan

          Hi Chris,

           

          With your data structured as it is above, I would probably do the following (it'll be a bit tedious, especially the more years you have, but it works!):

           

          • First, make sure all the "x" values turn into NULLs.  The easiest way, based on the sample data above, is to tell Tableau Prep that the fields are numeric (whole numbers).  Anything that's not a number will turn into NULL.
          • Then, for each year (thus the tediousness) create a calculation to replace the year:

           

          2018 will be the first non-NULL value working backwards:

           

          IFNULL([2018],

          IFNULL([2017],

          IFNULL([2016], [2015])))

           

          2017 gets a bit less complex:

           

          IFNULL([2017],

          IFNULL([2016], [2015]))

           

          2016 is even better:

           

          IFNULL([2016], [2015])

           

          2015, being the first year, doesn't even require a calculation

           

           

          Now, you may well have more years in your data and that's going to be more fun!  So if you went back to 2011, then 2018 will look like:

           

          IFNULL([2018],

          IFNULL([2017],

          IFNULL([2016],

          IFNULL([2015],

          IFNULL([2014],

          IFNULL([2013],

          IFNULL([2012], [2011])))))))

           

           

          If you had, say, a hundred years of data, I'd start to consider another approach in terms of pivoting the data and then doing some crazy joins and other logic,  It avoids the tediousness of writing a bunch of calcs, but as you can see going down that path does not reduce the complexity:

           

           

          Hope that helps!

          Joshua

          2 of 2 people found this helpful
          • 2. Re: Pulling Data forward when none is available
            Chris O'Hollearn

            This is GREAT!!!! I only am going back 5 years, so the calc you provided will work. Thank you so much for your help. I really appreciate it!!

            • 3. Re: Pulling Data forward when none is available
              Joshua Milligan

              You're welcome!  Happy to help!