Coming Soon! We’re launching a new sub-community within the Google Cloud Community dedicated to cloud security: The Google Cloud Security Community. In preparation for the launch, this site will be in read only mode from 22 September 12am PST - 23 September 7pm PST

Calculate last 7 day average

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?


1 0 231
Top Labels in this Space