6 Replies Latest reply on Mar 19, 2015 8:09 AM by Ojoswi Basu

# Fill in gaps between dates?

Hello,

I am connecting to an internal SQL database of company-level data. It is a time series - there is always a "Price" for each date but the "Estimate" field is sparsely populated. I am trying to find a way to fill in the gaps of the Estimate data. For example, in the attached packaged workbook Company AAA is missing estimates for 1/3/2015 and 1/4/2015. I would like to force the values on those dates to be the last occurring value (in this case 1.52 from 1/2/2015). Once a new value is reached (on 1/5/2015), I would like this estimate value to be repeated until the next time a new value occurs (on 1/10/2015).

I've tried using LOOKUP functions but have had no luck. I've attached a sample workbook. Would be immensely appreciative of any advice/tips.

Thank you in advance!

• ###### 1. Re: Fill in gaps between dates?

You can create a calculated field as such and bring that on the view instead of estimate and price.

• ###### 2. Re: Fill in gaps between dates?

Hi Luciana!

We can use the PREVIOUS_VALUE() function to accomplish this with a formula like below:

IFNULL(SUM([Estimate]), PREVIOUS_VALUE(SUM([Estimate])))

I also attached a revised version of your workbook.  Let me know if it looks alright

Thanks!

1 of 1 people found this helpful
• ###### 3. Re: Fill in gaps between dates?

Haha!  Great minds think alike, Pooja Gandhi!

• ###### 4. Re: Fill in gaps between dates?

• ###### 5. Re: Fill in gaps between dates?

Awesome! Thank you both! This saves me from an immense amount of grief

• ###### 6. Re: Fill in gaps between dates?

Hi Luciana,

I have used the Function ' PREVIOUS_VALUE' to create a calculated field as below

IF ISNULL(SUM([Estimate])) THEN PREVIOUS_VALUE(SUM([Estimate]))

ELSE SUM([Estimate]) END.

Find the attached worksheet for reference.

Best,

OJ