Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Date Filtering Using a Moving Relative Date Range

  • 5 October 2016
  • 4 replies
  • 2184 views
C

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

colin
Rank
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.

C

That worked perfectly! Thank you for the quick response!

A

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


    derived_table:


    sql: |


    SELECT


    MAX(dt) as dt,


    user_id,


    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


    FROM


    hike.user_dau_status


    where


    days(-6,0)


    GROUP BY


    user_id


Morgan
Rank
Userlevel 3
Badge

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):



SELECT 


r1.dt, 


r1.is_app_connect_dau, 


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.

Reply


Terms of Service