How to see Average calls per Minute over the last 30 days?

thwartted

I am importing data using a Federated Snowflake dataset. The dataset contains the following columns that I will use:

Call ID - the calls unique identifier

Date/Time - What time the call came in (dd/mm/yyyy - hh:mm:ss)

Call type - a category that states what the call was about

I am trying to see our average calls per minute when looking that the last 30 days. I can display this data when I am only looking at what came in during the last hour by counting the 'Call ID' column and then using 'Call Type' as a series:

But when I try to Avg(Count('Call ID')) using a beast mode (so that I can average the number of calls that came in each minute) it comes back with an error "we can't process this at this time..."

I imagine that I just don't know what I am doing. With it being a Federated Snowflake dataset, I don't believe I can do anything with the data via ETL, etc.

Godiepi

@thwartted

Ok, that's doable. You will need to do the following

1. Make sure you have a field that only have the Time Minutes for the day.
   1. I see you have Date/Time
   2. Extract the Time from it in this format hh:mm (do not include the seconds)
   3. You will have 1,440 points for each 24hrs
   4. This formula should do the job TIME_FORMAT(`Date/Time`, '%h:%i')
   5. This beastmode just created , use it as your x-axis
   6. Keep your column Date/time in the settings to plot Last 30 days or Last 31 days and exclude the current day (filter it out)
2. Calculate the Average
   1. in your case do, Count(Distinct `CallId`) / Count(Distinct DATE(`Date/Time`))
   2. dont divide by 30, dividing by Count(Distinct DATE(`Date/Time`)) will give you more flexibility in case you want to toggle between different time ranges... last 30days , last week , current month to date etc
3. You now have the 2 main points to fire up your chart
4. You can add 'Call Type' in the series but honestly, it will look like a mess even just without the series it is already too many data points plotted that create a lot of noise (consider using maybe 10 minute intervals of even 30 minutes, it will still tell you what the activity is during those times of a day)
5. also consider using the 'Call type' either as a quick filter or a card only for that call type that you can put next to each other.

I hope all this help you

GrantSmith

Hi @thwartted

You can't aggregate an aggregate in a normal beast mode. You'd need to utilize an ETL to aggregate your data with a count by whatever grouping you wish and then use that dataset to calculate the average in the beast mode in your card (or alternatively just calculate the average after your count aggregation in your ETL)

thwartted

@GrantSmith Domo doesn't allow for ETL's with Federated Snowflake datasets. Are there other solutions outside of ETL?

Godiepi

@thwartted ,

what are you expecting to see as far as visualization?

Are you trying to plot in a chart the average of calls per minute by call type for 30days ? that would be a long x axis with 43,200 minutes (there is 43,200 minutes in 30days)

or

you just want the single value (average calls per minute) by call type ?

in this case wouldn't it just be the count(distinct CallIds in 30days) / 43200 ?

thwartted

@Godiepi I am wanting to take the average traffic for each minute for the past 30 days. So I would see the day plotted out by minute and each minute would be an average of that minute over the past 30 days.

That way I could see at 2:01pm what our average traffic looks like for that minute over the past 30 days.

Godiepi

@thwartted

Ok, that's doable. You will need to do the following

1. Make sure you have a field that only have the Time Minutes for the day.
   1. I see you have Date/Time
   2. Extract the Time from it in this format hh:mm (do not include the seconds)
   3. You will have 1,440 points for each 24hrs
   4. This formula should do the job TIME_FORMAT(`Date/Time`, '%h:%i')
   5. This beastmode just created , use it as your x-axis
   6. Keep your column Date/time in the settings to plot Last 30 days or Last 31 days and exclude the current day (filter it out)
2. Calculate the Average
   1. in your case do, Count(Distinct `CallId`) / Count(Distinct DATE(`Date/Time`))
   2. dont divide by 30, dividing by Count(Distinct DATE(`Date/Time`)) will give you more flexibility in case you want to toggle between different time ranges... last 30days , last week , current month to date etc
3. You now have the 2 main points to fire up your chart
4. You can add 'Call Type' in the series but honestly, it will look like a mess even just without the series it is already too many data points plotted that create a lot of noise (consider using maybe 10 minute intervals of even 30 minutes, it will still tell you what the activity is during those times of a day)
5. also consider using the 'Call type' either as a quick filter or a card only for that call type that you can put next to each other.

I hope all this help you

thwartted

@Godiepi thank you so much! That worked like a charm! I changed the %h to %H, but other than that everything is perfect!