Need to count number of calls per 15 Minute Interval

In the attached workbook, I created two calculated fields to be able to view average call time in 15 minute intervals. The tabs are labelled Step One, Step Two and Step Three.

Step One

Create a calculated field that converts your dates into 15 minute intervals. There are likely many ways to do this, but my formula looks like this:

DATEADD(

    'minute',

    DATEDIFF('minute', [Start Date], DATETRUNC('hour', [Start Date])) % 15,

    DATETRUNC('minute', [Start Date])

)

The formula truncates the date to the nearest minute, than subtracts the correct number of minutes between it & the closest 15 minute interval. In the Step One, I display the original date and the converted date side-by-side, so that you can verify that the conversion is working.

Step Two

Create a calculated field that calculates the average call time. Formula is simply:

SUM([Minutes]) / SUM([Number of Records])

If you had a unique call identifier in your data, I would generally prefer this to be:

SUM([Minutes]) / COUNTD([Call ID])

but if your data doesn't have a unique Call ID, using the number of records should work as long as there is always one call per record.

Step Three

Verify visually. I plotted call volume for January 23rd, just to visually confirm that everything looks cool.

Hope this works for you,

-Steve