TS Filler Tool

To generate a series covering “every 3 weeks” you would set Interval to Week and Increment to 3.

Per the most common business usage scenarios, time series periods are handled as follows:

The only modification that the macro makes to the incoming data is: a "rounding" is applied to the [yourDateTimeColumn]. It is based on three key elements:

First, all fractions of the chosen interval are truncated off. For example, if you choose "Hour" as the interval, the macro will truncate any minutes and seconds from the date-time value, so for example. 1:58 pm becomes 1:00 pm.

After truncating the fractions of the chosen interval, the macro then determines what the valid periods are. The earliest date-time value entering the macro forms the basis of the series. Continuing the above "hour-series" example, if the earliest date-time in the [yourDateTimeColumn] is 1:58 pm, then the first period is 1:00 pm. If the chosen increment is 5, the subsequent periods are generated based on successively adding the chosen increment—5 hours—to the previous period, resulting in this case to 1:00 pm, 6:00 pm, 11:00 pm, etc.

Finally, now that all valid periods have been determined, all date-time values are then adjusted to the appropriate valid period. So a raw value of 5:59pm in the [yourDateTimeColumn] becomes 5:00 pm and 6:02pm becomes 6:00pm, etc.

After all date-time adjustments have been applied by the macro, the resulting [yourDateTimeColumn] is now effectively a "label" for that period. However, the data type is a date or date-time column (rather than a string), and as such this "label" may not be entirely intuitive. The key is to recognize that any portion of the date or date-time "label" that is "fractional" to the chosen interval should be ignored. So if you interval is "year", then an example period could be "2018-01-01" but it’s the year that matters in the label, the fractional portions of the label—the "01-01"—should be ignored, meaning that the period is simply the year 2018, or "any time in 2018". See the table below.

Note that, aside from the date-time “rounding” described above, the macro does not alter any incoming data. It doesn’t do any combining, summarizing, uniquing, or any type of validations on the incoming data. However, because of the date rounding, multiple measurements within the same period will now carry the same period label. As such, the data is now cleaner and can more readily be summarized because data can be “grouped by” this consistent, rounded period label.

Note that the macro does not account for Daylight Saving Time, so if you have time-level series data that crosses a Daylight Saving Time cutover, it’s possible that the macro will generate an hour row (or minute rows) representing an invalid hour. For example, areas that "Spring forward" one hour in March will have rows generated for the 2 am hour on the Spring forward day even though that hour never actually occurred (the clock switches from 1:59:59 to 3:00:00). This hour can easily be removed with a filter tool after the macro.

To clarify, the following table lists the interval options available, followed by what datatype is returned, and whether the period for that interval type is a "period starting" or "period ending" interval. The table then displays an example increment and "first raw value" in the series, to illustrate what the resulting period "Label" would be and an English description of what the period label actually means. Finally, the table notes which portion of the period label should essentially be ignored, like the portions that are fractional to the interval.