Date Filtering Using a Moving Relative Date Range

  • 5 October 2016
  • 4 replies

I am having difficulty coming up with a way to use the advanced date filter function to create a moving date range for the last 30 days excluding the present day. While it seems that it should be something as simple as 30 days to yesterday using the relative date syntax, but this format throws errors. Is there a specific format that I should use to be able to create my moving date range? Is there a specific format that will allow for something along the lines of {time} to {n} {interval} ago

4 replies

Userlevel 4

If you are referencing the advanced filter syntax, something like [30 days ago for 30 days] or [30 days ago for 29 days] should do the trick.

That worked perfectly! Thank you for the quick response!

I need to create a derived table that will calculate the count of something for each day minus 7 days for all the days

for e.g.

for 11th i need a count of all entries in between 5th and 11th

for 12th i need a count of all entries in between 6th and 12th

for 13th i need a count of all entries in between 7th and 13th

help me with this please

I have tried this, days doesn’t seem to work

  • view: user_mau_status


    sql: |


    MAX(dt) as dt,


    SUM(is_app_connect_dau) AS is_app_connect_wau,

    SUM(is_app_open_dau) AS is_app_open_wau,

    SUM(is_h2h_dau) AS is_h2h_wau







Userlevel 3

Hi Arsi,

There are a couple different approaches that you can take to calculate a rolling sum.

I’m assuming dt is your date column, is that right?

In that case, here is one approach that will get a rolling sum for is_app_connect_dau.

(Note that this will vary by dialect; this particular example is for MySQL):




sum(is_app_connect_dau) AS rolling_week_total

FROM hike.user_dau_status AS r1

JOIN hike.user_dau_status AS r2

ON datediff(r1.dt, r2.dt) BETWEEN 0 AND 7

GROUP BY r1.dt

ORDER BY r1.dt

The idea is to join the table to itself in a way that creates the 7 day intervals.

This example imitates one from this stack overflow post.