Solved

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

  • 18 February 2022
  • 3 replies
  • 1319 views

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?

 

icon

Best answer by Susan 18 February 2022, 21:49

View original

This topic has been closed for comments

3 replies

Hi Susan, Pls look into this article. It may solve this issue.

https://docs.looker.com/reference/field-reference/measure-type-reference#average_distinct

If not,  pls elaborate more about the issue. You have written distinct count of id however you want avg?

Yes, really what I am aiming for is an average of the daily bookings over a larger time period, but I cannot average the ids, because the id number is irrelevant.  

Average_distinct only averages the checkin id number so if I get two checkins one day, one with id 1000, and one with 2000, and the average would be 1500.  There were not 1500 checkins, those are just the ids of the checkins.

What I want to see is that the daily count would be 2 checkins. If the same thing happened every day the entire week, I would want to see the aggregate average 2. If on one day of the week, an id 3000 appeared, and not 2000, I would want to see 2 as the average, because each day there were still 2 checkins.  But if I were to use count distinct it would give me 3 for the week because there were 3 distinct checkin ids during the entire week.  Close but not what I need, and in the case of thousands of distinct checkins ids, the count distinct is very far from what I need

 

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