Sporadic Missing Dates In Time Series Data

Please take a glance at my missing dates below. Sometimes it’s one date and sometimes several dates in a row.

These dates were derived via Groupby Mean of how ever many properties closed each month of each year. Obviously, if nothing closed that month, then no mean date derived.

I found this workflow from several years back, but was wondering if there’s anything simpler in recent times. I’m using Missing Value Linear Interpolation for numeric missing data, but I can’t anything like that for dealing with missing dates.

I’m not worried about extreme accuracy for splitting the distance of exact days or anything. If I had some system to state the 15th of each missing month (and year), that would be enough to fill the gaps for a time series presentation of the data.

Thanks for any suggestions you may have.

Hi @creedsmith,

If you are happy to use linear interpolation to fill in the missing dates, then it would seem to me that turning dates into a serial number representation and then using Missing Values is a possible way to achieve this.

I have a component on the hub that I have just modified (as before it only accepted Local DateTime columns, but it will now accept a Local Date column too)

This can produce a “unix serial number” for the date, based on seconds since 1970-01-01. With this as a serial number, the Missing Values can then perform linear interpolation on it. Turning that into a Long , the standard node Unix Timestamp to Date&Time can convert the number back to give you interpolated dates:

Fix missing dates.knwf (59.2 KB)

To do this without the component, calculate the number of seconds since 1 Jan 1970 and multiply the result by -1, or take the abs() value using Math Formula, as follows:


Hi @takbb I will download your component and see if I can get it all worked out. Thanks for the help!

Hi @takbb I ran your WF linked above (thanks for that; it’s really nice to have the settings to work with). But my end dates ended up substantially in the future, like years 2080, 2083, etc. I will double check to see if I missed something on the settings

@takbb Never mind, just a stupid moron mistake on my part. 1900 in stead of 1970 :frowning:

Thanks for the super easy fix for what I would think will be helpful to many. Take care!


@creedssmith , Glad you found the issue and got it working.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.