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

# Pulling Data forward when none is available

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.

 Source 2015 2016 2017 2018 a 23 x 27 x b x 34 x 40 c 25 31 23 34 d x x x 20 e 20 x x 23 f x x 41 40 g 31 x 33 29

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.

 Source 2015 2016 2017 2018 a 23 23 27 27

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

Thanks,

Chris

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

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

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

You're welcome!  Happy to help!