1 Reply Latest reply on Sep 24, 2018 7:55 AM by Joe Oppelt

    Calculating averages for each of the 24 hours across all 12 months using discrete numerator but continuous denominator

    Mike Shi

      Hello all!

       

      I find myself with a challenging calculation that I haven't been able to find an answer for on the forums.

       

      I have a (continuously updating) data set that tracks log entries into the system on a discrete basis.  So an hour here and there each day may have several log entries, but not every hour of every day will have a log entry.  Each log entry has a number of minutes associated with writing that type of entry.  I am trying to build a viz that shows the average of these minutes across each hour of the day and each month of the year like so (with 24 hours across the top):

       

      tableau example.jpg

       

      However, the average calculated is only using the discrete occurrences of each hour as the denominator, whereas I would like it to calculate averages based on all possible occurrences of the specific hour in each month (my date range will always be the previous 365 from current date).   Ideally, I would like to be able to write a query that uses a DIM_TIME table with every possible hour of the last 365 days (so that my numerator can be continuous), but this table does not exist in the database I am pulling from.

       

      Is there a way to write an average calculation that uses the discrete numerator of my data and an appropriate denominator for how many occurrences of a specific hour in each of the 12 months?

       

      I've been struggling with this one for a while so would appreciate any help!

       

      EDIT

      I ended up having the DBAs create a DIM_TIME table for me so that I could do this in the query as it didn't seem like a possibility in Tableau.