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?
Thanks!