Solved: How do I average a daily count over week or month ... - Page 2

Susan1 · 02-17-2022 05:24 PM

I am new to Looker.

I have a table with a count of distinct values grouped by day.

I’ve imported into Looker as a view and I want to average the daily count over a month, but all I’ve come up with so far is the total count over a month.

SELECT 
   Date(checkinTime), 
   count(DISTINCT id) AS distinct_ids_count
 Group by Date(checkinTime)

I can’t use this because if I add the id then I have to group by it, at which point the count is 1, repeated over and over. But without the id column itself, I can’t join to the other tables where it’s a foreign key, so then the data becomes useless.

Is there a looker solution to a daily value averaged over longer periods of time?

Susan1

I managed to get something by getting a count of ids by day in my derived table SQL. The simplified version is this:

SELECT 
  Date(checkinTime), 
  count(distinct id) as daily_count
FROM checkins
GROUP BY Date(checkinTime)

Once I had that, in Looker I could aggregate the daily_count as an average over weeks, months, quarters, etc, as long as I had created a time dimension for checkinTime

View solution in original post

How do I average a daily count over week or month in looker where I need to join the data with other tables