5 Replies Latest reply on Sep 14, 2017 12:02 PM by JOHN MORALES

    Averaging a sum for a specific time period

    Andrew Fisher

      Hi all,


      I have some historical sales data that I'm trying to investigate in order to build up a picture of what an average day of online sales looks like.


      Each order is a single entry and has a date time that is associated with it.


      I've done a plot of day of week and hour against sum(Orders) and for the last week or this week it does as I would expect. However if I extend the period then it also does what I expect which is to sum up ALL the monday orders, all the tuesday orders etc.


      What I'd like to do though is to show for each hour in the day what is the AVERAGE number of orders that occur in that hour for each monday, tuesday etc. eg:


      Day       Date     Hour         Sum(orders)

      Mon      1/1        11             10

      Mon      8/1         11             5

      Mon      1/1        12             2

      Mon      8/1        12             5


      Would return me:

      Day        Hour       Average

      Mon        11          7.5

      Mon        12          3.5



      If I simply use avg(orders) I get the wrong answer (but expected) which is the average number of records for each order in that bucket which across the board is 1 - I understand why this is doing this (as each row is one order so it's correct).


      I'm thinking I need to do this in two steps which is to derive the number of orders in each hour block and then average each of those but I'm trying to work out how to get tableau to do that?