Calculate last 7 day average

  • 11 November 2021
  • 0 replies

I’m trying to calculate the last seven day average of unique users by day.  Then display that against thirty minute buckets for the current day.  This will provide a reference point for today’s performance.


I’ve tried this with a derived table and my dataset is too large to join back to the derived table and maintain performance.  


Here’s my attempt using window functions.  


I’ve tried avg(count(distinct ${TABLE}.visitor_id))) over (partition by ${ts_minute30})

But this won’t work as you can’t aggregate an aggregate.  


Here’s my attempt with table calucations.

I created an hour minute field to provide just the hour and 30minute of whatever is selected.  Then I also created a metric to only return the distinct visitors for that day. 


count(distinct (case when ${dt_date} = CURRENT_DATE('America/Los_Angeles') then ${TABLE}.id end))


Then I plotted these and created a table calculation for the mean(ID_count).

The view comes out like below.  The average still does not calculate per hour it calculates for the whole dataset.  


When I’ve done this with tableau in the past I could easily build an average like this with a fixed calculation.  fixed{date,hour : avg(count_distinct_ids)}.  Is anything like this possible in Looker?


Is there a better way to accomplish this?




This topic has been closed for comments