8 Replies Latest reply on Jun 30, 2015 3:03 PM by shanmukha eeti

    Calculating the average number of calls by weekday

    Ann Hartman

      I have a distribution of the total number of calls for each hour of each day of the week. I want the AVERAGE number of calls for each hour of each day of the week. I tried to just change the calculation from "sum" to "average," but I do not get a correct answer. I tried using "window_avg," but I don't really understand it, and I got an obviously incorrect answer. Can anyone tell me the calculation that I should use? I have attached a screen shot of the data. Thanks.

        • 1. Re: Calculating the average number of calls by weekday
          Mark Holtz

          Hi Ann,

           

          It sounds like you were on the right track and that you do in fact want a WINDOW_AVG function with a SUM function nested inside. The trick with all of the WINDOW_XXX functions is that they are "Table Calculations."

          Table Calculations have a second, less obvious element that is the dimension(s) they "compute using" as in which elements of your table.

           

          In the formula box, the text "Default Table Calculation" will appear when you create a new table calculation.

          table calc 1.png

          Clicking on that Default Table Calculation text will bring up the "Calculation Definition" dialogue box, and you will see each dimension in your view listed here for "Compute using:"

           

          This screenshot has a ton of dimensions, but you will likely see "Date" and "Interval start..."

          I am a little rough at doing these without using an example to play with, but if you just try choosing a one of those--say "Interval start...", you should get to the one that makes it work how you want.

          table calc 2.png

           

          There is also a section re: functions using Tableu Calculation in the knowledge base:

          http://onlinehelp.tableausoftware.com/current/pro/online/en-us/functions_functions_tablecalculation.html

           

          Hope this is helpful.

          Mark

          • 2. Re: Calculating the average number of calls by weekday
            Ann Hartman

            Thanks for the reply, Mark. I tried several variations of the "compute using" but I am still not getting the right number. For example, on the 4 Sundays in August between midnight and 1am, there were a total of 40 calls. Therefore, the average for Sunday from midnight to 1am should be 10. But I can't get to this answer. I have attached a workbook with data. I would really appreciate any help. Thanks.

            • 3. Re: Calculating the average number of calls by weekday
              Mark Holtz

              Hi Ann,

               

              It took a little tinkering, but I think this is what you're after.

              The confusing thing for me about table calculations is to try to consider how many "rows" the calculation is grouping things into.

               

              Since you had just the Day of Week vs. Hour of Day on your cross tab, the calc rolled everything up in each "cell" as evidenced on Sheet 2.

              So it was "starting with" the value of 40, not 4 different values.

              To "break" those 40 into the sub-components of 12,9,12 and7, you would have to add "Day" into the mix. See Sheet 3.

               

              So back on your main view, I added [Date] to the level of detail (I chose the Continuous Day value (the second set of Year, Quarter, Month...) and then flipped the measure back to discrete.  This gives 5/8/11 instead of 8 and guarantees the table calc can "see" the difference between the 4 values that make up the 40 as opposed to seeing a single value of 40.

               

               

              day.png

               

              Then, it's just a matter of tweaking the table calc's compute using option.

              I honestly don't know exactly how to explain what's going on, but I knew you wanted a value that would actually get values for each DISTINCT DATE and HOUR "cell" and that you would want to display them for each WEEKDAY and HOUR.

               

              The "At the level" tells the calc to evaluate for each date, even though it will display at the weekday level--so 4 values will be displayed "on top of each other." But, these are the same number. This causes a slightly bold look to the numbers.  You can try to play with the transparency in the Color control for the mark to attempt to deal with that.advanced.png

               

              If you turn the view into a graphical display instead, the bold quirk goes away... see Sheet 4.

               

              Hope this helps explain!

              Cheers,

              Mark

              1 of 1 people found this helpful
              • 4. Re: Calculating the average number of calls by weekday
                Jonathan Drummey

                @Ann - see the attached. The issue was that in the original set up there is only one value per cell (combination of hour and weekday) being returned, so 40 calls/1 = 40. What we need is to give Tableau the # of calls per day (per cell), then partition the WINDOW_AVERAGE calculation on the Hour and Weekday. I set up a calculated field for Day of Date - DATETRUNC('day',[Date]) and put that on the Level of Detail Shelf, then set the Compute Using (addressing) for the green Avg Calls per Day pill to the Day of Date, which causes Tableau to partition the calc on the Hour and Weekday. This returns overlapping text, so to remove that (and speed up computation) I altered the calc using Richard Leeke's technique:

                 

                IF FIRST()==0 THEN

                    WINDOW_AVG(Sum([Accepted]), 0, IIF(FIRST()==0,LAST(),0))

                END

                 

                @Mark - there are two places you can set the Compute Using, one is the Default Compute Using as you'd noted and the second is directly on the pill in the view through the pill's context menu. The Default Compute Using is only applied when the pill is first dragged into the view, changing it after that time has no effect on any instances of the pill that are already in use. For partitioning, you might read through the resources at http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/, particularly http://www.tableausoftware.com/sites/default/files/pages/table_calcs_in_tableau_6.pdf to get a better understanding of partitioning. If you'd like, I'd be happy to do a webex some time and walk you through some examples.

                 

                Cheers,

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Calculating the average number of calls by weekday
                  Ann Hartman

                  Thanks so much, Mark and Jonathan. I think both approaches got me to where I need to be (although I must admit that the table calculations are still way above my head). I really appreciate your time!

                   

                  Ann

                  • 6. Re: Calculating the average number of calls by weekday
                    Jonathan Drummey

                    Hi Mark,

                     

                    We were both working on this at the same time and came up with similar ideas, yours is simpler (by not adding the calculated field), and the Compute Using can be made even simpler.

                     

                    I'll attempt to explain: How your solution works is that with Hour of Starting Interval and Day of Date in the Compute Using, Tableau creates a set with the combination of those values and will generate a result for each of them. With At the Level set to Day of Date, that tells Tableau to increment the computation for each new combination of Hour of Starting Interval and Day of Date, which in this case is really the same as Deepest - you can change the At the Level to Deepest and you'll see that the results won't change. The Restarting Every is forcing Hour of Starting Interval to be part of the partitioning, which is added to the Weekday of Date that is already in partitioning. Therefore, you can simplify the calc even further by just setting the Compute Using to Advanced... and picking the Day of Date (i.e. putting that into the right-hand Compute Using window), which leaves both Hour of Starting Interval and Weekday of Date to be partitioning (i.e. still in the left-hand Available Fields window).

                     

                    This still returns multiple values (one for each Day of Date that's in the cell, so 4 records for Sunday in August 2012  from 12-1am). You can also see this by looking at the total marks in the lower left, Tableau is returning 774 marks for a when we'd expect to see only 175 (25 rows x 7 colums). The removing overlapping text technique in my other post is what I recommend rather than using transparency.

                     

                    In my post, I'd created a separate calc for Day of Date out of habit and had forgotten that I could get there with that flavor of an Advanced... Compute Using, I'm so used to battling with Tableau's date padding and other oddities with dates and table calcs that I habitually create my own fields to have more control. Thanks for the reminder!

                     

                    Jonathan

                    • 7. Re: Calculating the average number of calls by weekday
                      Manisha Patel

                      Hi Jonathan,

                       

                      I hope you can help me.

                       

                      I have used your Window_Avg calculation for one of my reports I’m creating which looks at the average calls by weekday by hour.  See attached.

                       

                      Your calculations worked wonders for my report however I’m trying show the data in a heat map …so highlighting where the high volumes of calls are occurring during any week within a month.

                       

                      I inserted the calculation again but changing the mark to colour and to square but that’s not bringing back what I want to see.  The third worksheet is what I want to see but with squares as well. I hope this makes sense and any help is much appreciated.

                       

                      Thanks

                      • 8. Re: Calculating the average number of calls by weekday
                        shanmukha eeti

                        This works,But the only problem is when you import the data or create charts with it,is there a workaround for it.