Average Number of Logins for Weekday and time instead of Date

Q&A
, , ,
1

I’m trying to get the average number of logins per weekday and hour. (i.e, on average, how many users are logged into the platform at 3PM on Mondays vs. 12PM on Saturdays)

We have a user_logged_in that has date and timestamps and then I’m trying to an average of the user_ids that logged in during that time.

1 Like
2

Hello @DataDude1, yes, you can accomplish this functionality using LAC-W aggregations. We can calculate the total number of logins for each day and each hour, then nest those values in an average calculation. First, lets create 2 calculated fields to return dates at the day level and hour level.

Date Hour = truncDate('HH', {Date})
Date Day= truncDate('DD', {Date})

Total Logins by Hour = distinctCountOver({user_ids}, [{Date Hour}], PRE_AGG)
Total Logins by Day = distinctCountOver({user_ids}, [{Date Day}], PRE_AGG)

Then, if you wanted to set this up to filter by a specific day or hour, you could use this calculation instead of returning user_ids directly:

Users on Mondays at 3pm = ifelse(extract('WD', {date}) = 2 and extract('HH', {date}) = 12, {user_id}, NULL)

Avg Logins for Users on Mondays at 3pm = avgOver(distinctCountOver({Users on Mondays at 3pm}, [{Date Hour}], PRE_AGG), [], PRE_AGG)

The last function won’t exactly be dynamic, but you can make this functionality by using parameters that the user can select for both dates and comparing the returned values in the same way. That last function would count user ids per hour (i.e. per monday at 3pm), then average it across the filtered values. I’ll link some documentation, but let me know if this helps!

1 Like