Dynamic number of occurrences of weekday calculation

[alexjohndean]

Hi,

I have a bunch of call centre data that I'm working with. One of the things I want to do it be able to calculate average call volumes by day of the week. For example, if we had the below total call volumes for the month of April:

Monday: 25,042
Tuesday: 27,684

I want to know what the average number of calls that came in on a Monday in April was (there are 5 Monday's in April so the number I want to get to is 5,008). I can't for the life of me figure out how to calculate the number of occurrences of a specific weekday within a filtered time window.

Any help here would be much appreciated.

Alex

[Mick-03]

Hi @alexjohndean ,

Here’s a simple idea from me.

Assuming that the call data is recorded daily without any gaps,  you can define a measure with the distinct count of dates by day of the week and calculate the average call volume using a table calculation.

This is just one simple approach, and there might be other methods to be considered.
I hope this helps!

[Emma_Docs]

Hey there!

I'm wondering if we could do this with a little creativity with pivoting and averages.

I'm assuming you have (or could create) something like these two fields:

dimension_group: call_date {
  type: time
  timeframes: [date, week, month, day_of_week]
  sql: ${TABLE}.your_date_field ;;
}

 By using the day_of_week time type we'll be able to quickly distinguish the Mondays, Tuesdays, etc. The second field we'd need is an average call volume metric:

measure: avg_calls {
  type: average
  sql: ${number_of_calls} ;;
}

With these two fields we can pivot and create the following table:

Hope this helps!