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

    Average Calls per Hour per Weekday

    Stanley McLenna

      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
          Shawn Wallwork

          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
            Stanley McLenna

            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
              Shawn Wallwork

              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
                Stanley McLenna

                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
                  Shawn Wallwork

                  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
                    Tracy Rodgers

                    Hi Stanley,

                     

                    Here is a temporary link to the videos:

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

                     

                    The domain padding video should be the first link.

                     

                    -Tracy

                    • 7. Re: Average Calls per Hour per Weekday
                      Stanley McLenna

                      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:

                      missing_count_values.PNG.png

                      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
                        Shawn Wallwork

                        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
                          Stanley McLenna

                          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
                            Shawn Wallwork

                            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
                              Stanley McLenna

                              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
                                Shawn Wallwork

                                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
                                  Jonathan Drummey

                                  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
                                    Stanley McLenna

                                    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.

                                     

                                    Very helpful reply, thank you.

                                    1 2 Previous Next