1 2 Previous Next 15 Replies Latest reply on Jun 8, 2013 6:59 PM by Jonathan Drummey

# Average Calls per Hour per Weekday

How can I average the number of calls received per hour per day of week? For example, I can average the number of calls received per weekday perfectly fine, i.e. Mondays average 25 calls, Tuesdays average 23 calls, etc., by taking the total number of calls received on any day and then dividing that by the number of each of those days that occur in my time range:

SUM([Number of Records])/[Total Number of Specific Days]

However, when I include the number of hours, it will sum the total number of calls received each hour but some days won't have calls in those hours thereby reducing the number of days it divides by. For example, one Monday may have 13 calls during the noon hour, but the previous Monday didn't have any calls so the average is being calculated as 13 calls, when it should be 6.5 since there have been two Mondays. Since there isn't any data for the noon hour for one of the Mondays, it's not counted as a day. How can I force the average to still count the total number of Mondays no matter if there were calls made during a specific hour or not?

• ###### 1. Re: Average Calls per Hour per Weekday

It depends on a whole lot of things but you can start playing around with something like this:

IF DATENAME('weekday',[Order Date])='Monday' THEN 1 ELSE 0 END

In certain situations that will give you a count of Mondays.

--Shawn

• ###### 2. Re: Average Calls per Hour per Weekday

That would work if I could use the source data field, but I need to make sure all Monday's are accounted for in my timestamp filter even if the data doesn't record anything happening for one of those Monday's.

• ###### 3. Re: Average Calls per Hour per Weekday

You're looking for domain padding. Joe Mako did a TDT on this last month. He's also doing a second one tomorrow.

Here's the link to the last one: https://tableausoftware.webex.com/tableausoftware/lsr.php?AT=pb&SP=EC&rID=32308267&rKey=c7468ecc3e12a14e

--Shawn

• ###### 4. Re: Average Calls per Hour per Weekday

The link doesn't work; will you repost it? Thank you for pinpointing somewhat what I need; I'll do some research on domain padding and hopefully get something that works for me.

• ###### 5. Re: Average Calls per Hour per Weekday

Sorry about that Stanley, Tracy Rodgers mention the links were broken, but I thought she meant the ones on the viz. She'll let us know when they're fixed.

Here's a good place to start:

http://community.tableau.com/message/199032#199032

Hope this helps.

--Shawn

PS: Here's a better Tableau search engine http://community.tableau.com/search?q=domain+completion

• ###### 6. Re: Average Calls per Hour per Weekday

Hi Stanley,

Here is a temporary link to the videos:

https://www.dropbox.com/sh/4zlzjs8i7itzl98/S_SI3G_nfe

-Tracy

• ###### 7. Re: Average Calls per Hour per Weekday

I watched the video and it was extremely helpful. From that, I created my missing hour values for my entire time range, however, the next issue is making the Number of Records sum I'm using to default to 0 instead of blank for the hours that are generated. Here's a screenshot to show an example:

If I can get Calculation1 to show zeroes instead of blanks, will the "Average per Day" work as expected and actually average days with a zero count?

• ###### 8. Re: Average Calls per Hour per Weekday

Try putting ZN() around calculation1, it may or may not let you do that.

--Shawn

EDIT: Probably, but we're playing chess here without any pieces, just the board.

• ###### 9. Re: Average Calls per Hour per Weekday

That didn't work either; is there a support line I can call? This is pretty crucial and I'm stumped how to fix and how to explain.

• ###### 10. Re: Average Calls per Hour per Weekday

Support? Well I'm not sure. Maybe Tracy Rodgers can send you in the right directions, she use to work in support.

--Shawn

• ###### 11. Re: Average Calls per Hour per Weekday

I also tried using this function:

ZN(IF([Number of Records] == 0 OR ISNULL([Number of Records])) THEN 0 ELSE [Number of Records] END)

I must be getting desperate but this doesn't work either. Any other ideas to try?

• ###### 12. Re: Average Calls per Hour per Weekday

Stanley, if you're truly desperate, send me your workbook at myname.hotmail.com and I can take a look.

--Shawn

• ###### 13. Re: Re: Average Calls per Hour per Weekday

When you are using domain padding i.e. Show Missing Values, functions like ZN(SUM([Number of Records])), or ISNULL(SUM([Number of Records])) don't return the expected results because they are computed in the data source, before domain padding occurs. If you are just looking to display 0's, then you can use Format->Special Values->Text to display 0. But I'm thinking you want to access those values, in which case you can use ZN(LOOKUP(SUM([Number of Records]),0)). The LOOKUP() is a table calculation that occurs after domain padding, and returns Null if there is no value, which then gives the ZN() - which is computed in Tableau - something to work with and you'll get 0's.

One thing to know when you are using domain padding is that Tableau only pads between the start and stop of the dimension being padded (within the view), so for example in the screenshot you posted the 5/21/13 data is only padded between 9am and 11pm.

From your description, you do need to pad by the day, the only question is if you have any hours that would not have calls in the time period you are looking at (more on that at the end). The attached uses some dummy data I grabbed. I set up a couple of filters so there is a missing day in the data and no data for 1am, so Show Missing Values is turned on for both the Day of Date and Hour of Date.

There is at least one record for each hour of the day in the time range being looked at, but not necessarily a day in the data, so Show Missing Values is turned on for days.

The SIZE() table calculation is used to get the # of Days, with an Advanced Compute Using of  Weekday of Date, Day of Date, and Hour of Date (in that order) with Restarting Every Weekday of Date and At the Level Day of Date. That's a mouthful, what it does is count the number of days for each weekday, an effectively ignoring the hour.

The Total per Hour calculation is ZN(TOTAL(SUM([Number of Records]))) and has an Advanced Compute Using of Day of Date, so it partitions on the Hour of Date and Weekday of Date.

Then the Avg per Hour per Day uses the following formula: IF FIRST()==0 THEN [Total per Hour]/[# of Days] END. This also uses a Compute Using of the Day of Date, so it partitions on the Hour and Weekday, returning only one result for each Weekday/Hour combination.

With this, you can then build something like the bar chart or line chart views. Note that I put a duplicate of the Avg per Hour per Day field on the Filters Shelf, filtering for non-Null values. The line chart is a good candidate for using color highlighting, with all those marks it gets a little busy.

Ok, what about the question of not having records for ever hour you want to report on? There are two different situations here:

1) You do have the beginning and end of the time range you want to report on in the data. This example has that going on, and everything works fine.

2) You do not have the beginning and/or end of the time range you want to report on in the data. In that case, you're going to need to either pad the data outside of Tableau, or build a scaffold data source.

For a situation like this, I'd probably use a scaffold source. Whipping up a table in Excel with every day/hour combination for several years takes a few seconds, then you could make that the primary and use your data set as the secondary and then use table calculations above (only the Number of Records would come from the secondary) and not have to worry about any sparse data issues.

Jonathan

2 of 2 people found this helpful
• ###### 14. Re: Average Calls per Hour per Weekday

Thank you very much for your help Jonathan. Most of what you said was over my head and it took a few times for me to comprehend it, but your last paragraph was probably the easiest and least painful solution. I generated a spreadsheet of dates spanning from January 1st, 2013 to another four years, then I used data blending to JOIN the two sources together, then tally the matches on each hour. After writing a few calculations I was able to view the average number of calls per hour per day of week over a time period. The customer also wanted to show the number of employees working during each of those hours so I used a dual axis approach to map one over the other.