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?
Solved! Go to Solution.
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