Klipfolio Tips: Generating Weekly Date Sequences

It’s often useful in Klipfolio to create a sequence of dates for graphs or tables, spanning back in time from today to X years ago.

Use the following code to generate such sequences:

DATEVALUE(
    group(
        DATE_STARTOF(
            DATERANGE(
                DATE_STARTOF(TODAY(), "1", -2),
                today()
            ),
            "4"
        )
    ), "yyyy-MM-dd"
)

On line 5, the “1” represents YEAR. Only line 8, the “4” represents WEEK. In the Klipfolio interface these are displayed as proper values.

Change the date format string on line 10 to be whatever you need.

Ed Brocklebank
Ed Brocklebank (aka Metric Mogul) is an analytics and digital marketing consultant. He helps business of all sizes become more data-driven through measurement, strategy and activation. He works as a Strategic Analytics Director at Jellyfish in London, as well as delivering training on behalf of Google and formerly General Assembly.