4 Replies Latest reply on Oct 22, 2013 3:06 PM by Stanley McLenna

    Average of Calls over Weekday

    Stanley McLenna

      I'm trying to average the total number of calls we get over each hour and each weekday. For example, business wants to see the average number of calls received in the 07:00 hour for all Sundays, so for example the calculation I wrote looks like this:

       

      SUM([Number of Records])/COUNTD([Weekday])

       

      I expected this to count all of the occurrences of any specific day (for example I pick Sunday's from my date filter) and it would use that number as the denominator over the total number of records. Tableau will only count the Sundays that have had calls when in fact I want it to count all the Sundays in my dataset, whether they've had calls on them or not. How do I configure the report to treat the denominator like that?

        • 1. Re: Average of Calls over Weekday
          . Indumon

          Hi Stanley,

           

          I think you can accomplish your results by creating a weekday calculated field.

           

          Calculation 1: Weekday ( This calculation will return the weekday of each date)

           

          Case DATEPART('weekday',[Order Date])

              When 1 Then 'Sunday'

              When 2 Then 'Monday'

              When 3 Then 'Tuesday'

              When 4 Then 'Wednesday'

              When 5 Then 'Thursday'

              When 6 Then 'Friday'

              When 7 Then 'Saturday'

          End

           

          Calculation 2: Avg no of Calls

          Sum([Number of Records])/Countd([Weekday])

           

          Hope this will help you

          • 2. Re: Average of Calls over Weekday
            Jim Wahl

            Hi Stanley,


            I saw your question this morning and was curious as to how to solve the question: How many Sundays are there between two dates?

             

            Just to make sure I understand your problem, I created a few random rows of data for the months of December 2012 and January 2013 with just one column for the timestamp. Given your example above, each row would be a call. Below you can see the number of records for each day. Some days have multiple calls, some have none. (You can right-click Date to create the various dimensions. The calendars on the right were just cut and pasted into the image.)

            2013-10-17 09-42-08.png

             

            There are 5 Sundays in December, but only 4 have data. January has 4 Sundays; 3 with data. COUNTD(DATE(Date)) returns 4 and 3, respectively. You want 9, but are getting 7.

            2013-10-17 09-57-07.png

             

            While there are techniques to have Tableau fill in missing members (rows) of a domain (dimension) by using Show Missing Values, it's not going to help here and I don't think in general it'll work in this situation. And at any rate, it's easy to use some math.

             

            Dates in Tableau are just floating point values starting at 0 for 1900-01-01 and adding 1 for each full day: 1900-01-02==2, ... up to 41,562 for TODAY(). If you divide the dates by 7, you get the number of weeks, and now you're getting close to counting the number of Sundays, ... .

             

            The basic strategy is to subtract the start date from the end date and convert the days to weeks. There are a couple of tricks and general points to be aware of.

             

            1. Tableau weeks start on Sunday (weekday == 1), but numerical weeks start on Monday (date % 7 == 0).

             

            2. If the weekday of the start date is the same as the day you want to count, you need to add 7 to the end date:

            Example 1: Count Sundays between Sunday, 2012-12-02 and 2012-12-02.

            In this case the count should be 1.

            Date NumWeek Num (Date Num / 7)
            Start DateSunday2012-12-0241,2435,891.86 (5/6ths)
            End DateSunday2012-12-0241,2435,891.86
            End Date + 7Sunday2012-12-0941,2505,892.86

             

            Subtracting these two weeks gives us 0. So we need to add 7 to the end date to make it 5892.96.

             

            Keeping the weekday of the start date as Sunday, you'd need to add different amounts to the end date for the math to work: 

            Weekday of start dateWeekday to countAdder
            SundaySunday7
            SundayMonday6
            SundayTuesday5
            SundayWednesday4
            SundayThursday3
            SundayFriday2
            SundaySaturday1

             

            4. As you change the weekday the start day, you also need to change the adder:

            Weekday of start dateWeekday to countAdder
            SundaySunday7
            MondaySunday1
            TuesdaySunday2
            WednesdaySunday3
            ThursdaySunday4
            FridaySunday5
            SaturdaySunday6

             

            Example 2: Count Sundays between Monday, 2012-12-03 and Monday, 2012-12-03.

            In this case the count should be 0.

            Date NumWeek Num (Date Num / 7)
            Start DateMonday2012-12-0341,2445,892
            End DateMonday2012-12-0341,2445,892
            End Date + 7Monday2012-12-1041,2505,893
            End Date + 1Tuesday2012-12-0941,2505,892.14

             

            The basic formula for the adder to the end date: DoW Adder =

            DATEPART('weekday', [Start date] - [Day of week to count])
            

             

            Day of week to count is a parameter:

            2013-10-17 10-40-25.png

             

            Now you can use DoW Adder in Count of Days =

            INT( (([End date] + [DoW Adder]) - [Start date]) / 7 )
            

             

            Again, End date and Start date are parameters. You can derive these from the view using DATE(WINDOW_MIN(MIN(Date))) and DATE(WINDOW_MAX(MAX(Date))), respectively. There is a problem with this approach, however. Tableau is finding the min / max dates where there is data. In the above example above, January 30 and 31 don't have data, so DATE(WINDOW_MAX(MAX(Date))) == Jan 29. (The DATE() wrapper function truncates the time.)

             

            Attached is the workbook for the above examples.

             

            Jim

            • 3. Re: Re: Average of Calls over Weekday
              Stanley McLenna

              Wow, thank you both for your replies; I need to read those over again to fully digest what you're doing. The first solution proposed was the same solution I implemented essentially, however it still only counts the days that have data.

               

              I still have to read over the second solution to get what you mean, but to make the data plot correctly I joined data from the datasource to a CSV file containing every hour for the next two years to cover all of the hours/days that don't have data. Basically, I do a left-join against that date source to get all the dates and I want it to include the hours that don't join to anything in the graph. If I count the number of days between two dates with that generated CSV file, it shows the correct number of Sundays, Mondays, etc. I just want it to appear in the graph. Here is the SQL generated by Tableau for the left joins:

               

              SELECT [DateGenerated#csv].[DateTime] AS [DateTime],

                [Call Log_test#csv].[CALL ID] AS [CALL ID],

                [Call Log_test#csv].[TIMESTAMP] AS [TIMESTAMP],

              [Call Log_test#csv].[TimeStamp Hour-Only] AS [TimeStamp Hour-Only],

                [ActiveAgents#csv].[Date] AS [ActiveAgents#csv_Date],

                [ActiveAgents#csv].[Number of Agents] AS [ActiveAgents#csv_Number of Agents]

              FROM ( [DateGenerated#csv]

                LEFT JOIN [Call Log_test#csv] ON [DateGenerated#csv].[DateTime] = [Call Log_test#csv].[TimeStamp Hour-Only] )

                LEFT JOIN [ActiveAgents#csv] ON [DateGenerated#csv].[DateTime] = [ActiveAgents#csv].[Date]

               

              I want to do a COALESCE (like in a database) on the "Call ID" column so it always returns a value and is always counted. Make sense?

              • 4. Re: Average of Calls over Weekday
                Stanley McLenna

                After carefully reading your explanation I was able to get the chart to process correctly and calculate an average based on the total number of weekdays between the start and end dates. Thank you both very much.