12 Replies Latest reply on Nov 15, 2016 7:15 AM by Anthony Paquet

    Day of the week/Hour heatmap with average

    Anthony Paquet

      Hello everyone,

       

      I have prepared a heatmap showing the busiest hours for our dispatch center, based on the number of records in the phone log table and the date and time of each entry. The data was filtered to use records from 2015 and 2016.

       

      The coloring matches the number of records. My problem is that I'd like to label each square with the average number of calls per time period for the time period covered by the square (i.e. Monday 7 to 8, Thursday 10 to 11, etc.)

       

      I've tried multiple things (window_avg, table calculations, etc.) but can't seem to find anything that works.

       

      I can't post my workbook as the data it contains is too sensitive.

       

      Thanks for helping me out!

        • 1. Re: Day of the week/Hour heatmap with average
          Matt Lutton

          You might consider posting a workbook example using a sample dataset, like Superstore Sales.  You're just far more likely to get an accurate response if a TWBX is attached, and this particular example is fairly easy to replicate using the Superstore data that ships with Tableau. I'll be happy to come back and take a look if a workbook is posted -- thanks!

          • 2. Re: Day of the week/Hour heatmap with average
            Michael Hesser

            Hi Anthony-- you're almost there!

            I created a very similar view (seen here):

             

            calmap.jpg

             

            This is a heat map, but I dropped AGG([Display Value]) onto the Label to get the text to appear.

            Would this work for you?

            • 3. Re: Day of the week/Hour heatmap with average
              Anthony Paquet

              Hello Michael,

               

              I was already able to label the heatmap. Here's an example with the total number of records for each timeframe :

               

              The thing is, I don't want the sum of records but the average number of records (phone calls) within that day of the week/hour timeframe. Using AVG instead of SUM only brings up "1" as a result for every timeframe. Basically, the result would have to come from the sum of existing records for this timeframe divided by the number of days covered by the data (which will change depending on my filter settings).

               

              As for using a workbook with sample data, none seem to contain the type of data I need (logs with date and hour of an action).

               

              Thank you!

              • 4. Re: Day of the week/Hour heatmap with average
                Michael Hesser

                Hi Anthony;

                 

                Some of this will depend on how your data is organized.

                 

                I think the big question is: how do you computer number of phone calls? Do you have an ID number, a field with a value in it, or is every row a unique call?

                Depending on how you are logging a unique call, your equations may vary.

                 

                I think you can solve this using a LOD expression:

                 

                Try this: {FIXED:SUM([Number of Records]}

                 

                If this doesn't work (and you have two distinct fields: one for DATE and one for HOUR)

                • Drop DATE onto rows and display as WEEKDAY
                • Drop HOUR onto Columns
                • Drop SUM(Number of Records) into Color and Label

                 

                Again, if you have a FIELD that is the number of calls, try the average of that, If you have a unique ID, try COUNTD([ID Number]).

                 

                I hope this helps a little-- again: sharing your fields witha TWBX will be a big help!

                • 5. Re: Day of the week/Hour heatmap with average
                  Anthony Paquet

                  Hi Michael,

                   

                  Indeed, each call has its own row/record in the table I use as a data source. As the last screenshot I have posted shows, "Heure de l'appel" (Time of call) is used, in my workbook, for both rows (using the Weekday function) and columns (using the Hour function). Total records for each timeframe are calculated and displayed properly. What I am unable to display, however, is not the total number of records for the timeframe but the daily average for the same timeframe.

                   

                  Let's consider Friday at 2AM. My screenshot shows a total of 4003 records for this period. Now, if my data covers 100 days, it means there is an average of 40 calls on a Friday night between 2 and 3AM. 40 is the number I would need to see, and not 4003.

                   

                  For this purpose, "FIXED" does not seem to provide a solution.

                   

                  I'll try to figure out if I could find a way to manipulate the data as to post a TWBX but for the moment, the data contains personal information I can't post.

                   

                  Thanks!

                  • 6. Re: Day of the week/Hour heatmap with average
                    Michael Hesser

                    I see! You just need to get a little trickier in your LOD calculations.You need to compute how many days are in your set, and divide the sum (for that weekday, for that hour) by that amount.

                     

                    So based on your description above, you need to determine how many days are in your sample (that's how many days TOTAL, rather than how many of each weekday).

                     

                    I think you can get to that value by stripping the hours off the datetime and using a countd:

                     

                    Please see if this equation works for you. Note that your sum(number of records) will be specific to the WEEKDAY/HOUR.

                     

                    It is likely that you will need to change this to a LOD expression.depending on how you want the data to aggregate.For example: do you want the count of all Fridays? Or just Friday calls from 1-2? This should give you the latter.

                     

                    Please give it a try. There may be more elegant solutions for you

                    • 7. Re: Day of the week/Hour heatmap with average
                      Anthony Paquet

                      Hello Michael,

                       

                      I ended up finding a similar yet maybe (actually very) less elegant solution (hence why I didn't mark the question as solved before). I counted the difference between the "minimum value" and "maximum value" in the date column within the dataset :

                       

                      DATEDIFF('day',MIN([Date de l'appel]), MAX([Date de l'appel]))

                       

                      Then divided the number of records by the result of the previous calculation.

                       

                      It works almost as expected, to the exception that the DATEDIFF sometimes gives a different number of days for a certain timeframe (e.g. no calls on Monday at 5AM for the 10 first days of the filtered data will divide the number of records by 355 while other timeframes will be divided by 365). It is something I can live with.

                       

                      Thank you for your help!

                      • 8. Re: Day of the week/Hour heatmap with average
                        Michael Hesser

                        Hi Anthony! I'm back with a refresh and armed with some new info after a great session at TC16 with Blake Nicholson and Reed Sweeney.

                         

                        HOPEFULLY I learned a thing or two from their fantastic LOD class and can solve your problem!

                         

                        I created a quick data set which I'll use in my attachment, and rather than just give you the answer (or what I think the answer is), I'm going to walk you through some of the best practices I learned during the conference.

                         

                        Before I jump in and start writing an LOD calc, it's good to get a picture of the data. By doing this, it's easier to compare the results of the LOD calc to make sure it's in agreement with what you expect it to be.

                         

                        I started by listing the WEEKDAY and each DATE:

                        As you can see in my tiny sample set, most weekdays only have two entries. The one exception is Thursday which has but one date associated with it.

                         

                        As I said before, this is going to be the "scaffold" that I compare my LOD calc against to make sure it's doing what I want.

                         

                        Next, I need to craft my LOD calc. If you're like me, you may find it easier to do this in segments. While this may mean more calcs in the long run, it can help you ferret out any errors.

                         

                        As we look at you problem we realize we need to know for each weekday, how many unique dates were found? In other words, we need a calc that will tell us there were 2 unique dates for every weekday except that rascally Thursday, which had only 1.

                         

                        The first part of our LOD calc is determining the dimension we want to calc on, as as you can see from my slyly worded description, the dimension level is WEEKDAY. We start our LOD with that: HINT: it's a lot of typing to enter all that "weekday" stuff. You can drag and drop it from rows and it will fill it right in-- easy peasy!

                         

                        Count of Unique Dates

                        {FIXED DATEPART('weekday',[DateTime]:}

                         

                        But we're not done yet. We still need to count unique dates (emphasis on DATE). Our current [DateTime] includes time, and we need to strip that out somehow so we can count just the instances of each unique date, not each unique date/minute/hour/second. We can strip it to just the DATE by using the DATE function:

                         

                        DATE([DateTime])

                         

                        And then we can count unique instances using COUNTD:

                         

                        COUNTD(DATE([DateTime])

                         

                        This becomes the aggregate expression of our LOD calc, so our final product looks like this:

                         

                        Count of Unique Dates

                        {FIXED DATEPART('weekday',[DateTime]):COUNTD(DATE([DateTime])}

                        //Note: this was previously missing a parenthesis, which I have now added

                         

                        Whew! Now let's plug it in against our "scaffold" and see if it gives us the values we want::

                        This looks pretty good. As you can see, the LOD count reflects the number of dates for each weekday.

                         

                        It's probably good to run one more test, though. HOURS are going to be a central part of this viz, so we should check that the values don't change when we throw hours into the mix.

                        I created a third view, this time including hours:

                        This test shows that-- even adding hours-- our LOD calcs remain the same. We can move on!

                         

                        The next step you may have figured out already. We just need to get the average calls by weekday. IMPORTANT: my data set varies from yours, but you can make the necessary modifications to get the values you need. Instead of SUM([Calls]) you'll probably use SUM([Number of Rows])

                         

                        Average Calls

                        SUM([Calls])/SUM([Count of Unique Dates])

                         

                        Note that the reason we're "summing" our LOD expression is to turn it into an aggregate.

                         

                        We drop this into a heatmap and we get our result:

                        1 of 1 people found this helpful
                        • 9. Re: Day of the week/Hour heatmap with average
                          Michael Hesser

                          I'm glad you found a solution. My last long reply should take those instances into account (for example, if you never got calls on a particular day in the set).

                          You may reconsider using it to account for weirdness in your sets (leap days,incomplete weeks, etc.)

                          Whatever you choose, you may want to mark your answer correct so other people don't jump in and try to solve your issue

                          • 10. Re: Day of the week/Hour heatmap with average
                            Anthony Paquet

                            Michael,

                             

                            This is one amazingly detailed answer!

                             

                            I'm still running into a problem (I fear I am not enough of a Tableau ninja yet) when adapting your formula to my data :


                            I'll make sure to mark the question as resolved as soon as I sort this out. I'm pretty sure this will cover what I needed.

                             

                            Thanks again!

                            • 11. Re: Day of the week/Hour heatmap with average
                              Michael Hesser

                              Oops! I left a parenthesis out!

                               

                              Please try this:

                               

                              Count of Unique Dates

                              {FIXED DATEPART('weekday',[Date d l'appel]):COUNTD(DATE([Date d l'appel])}

                               

                               

                              Please check my spelling for your field name, too Good luck.

                              You may also want to reference my TWBX if you can download it... it should be in working order there.

                              I'll edit my response above to include the missing paren.

                              • 12. Re: Day of the week/Hour heatmap with average
                                Anthony Paquet

                                Thank you so much! I did play around with the parenthesis yesterday but I'm not familiar yet with the syntax for LOD calcs.

                                 

                                There was only one thing that did not work for me once I got the formula right : no matter how I filtered the range of dates to be used, the calculation did not seem to ajust. After searching the forums, I learned that if you want a filter to apply to a LOD calc, you have to add it to your context. I think it's a good thing to mention this here in case others come across the same problem.

                                 

                                Thanks again for your help Michael, I really appreciate it!