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.  

6b89aec5-8fa8-4013-920f-4e63fa0d080b.png

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?

Thanks! 

1 0 428
0 REPLIES 0
Top Labels in this Space
Top Solution Authors