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

# Averaging a sum for a specific time period

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?

Cheers

Andrew

• ###### 1. Re: Averaging a sum for a specific time period

Hi Andrew,

Would you be able to post a sample workbook? (twbx file)

I have attached a workbook using the data your provided and have gotten the desired results, however, I think I might be missing something.

-Tracy

• ###### 2. Re: Averaging a sum for a specific time period

Hi Tracy,

I've attached a sample workbook to explain this a little more. Looking at your data set, if I had the orders summed by hour already then that would work as you've got it which would be ace. Unfortunately I have my orders in "real time" - ie right down to the second on when they were placed.

This means I need to do two steps - the first is to sum the orders for a given hour on a given day (which would give me the data as you've used) and from there I could use an operation such as you've done in your wookbook to give me the averages over the period in question.

Hopefully the workbook I've attached shows this further. I've just shown 3 hours worth of data for 2 days of the week but have also simulated one of the issues which is that on any given day of the week my "previous period" may comprise more or less of those days in the sample (eg in this set 3 sundays but only 2 mondays).

I could potentially push the summing operation back to the database using some sort of group by hour by date or something but my feeling is that tableau should be able to handle this type of analysis.

Cheers

Andrew

• ###### 3. Re: Averaging a sum for a specific time period

Hi Andrew,

I have reattached your workbook with the desired view (I think/hope!) and directions.

-Tracy

• ###### 4. Re: Averaging a sum for a specific time period

Thanks Tracy - much appreciated. Worked really well though I had to update the hour to use the cell too as there were different numbers of hours represented across the time period but that was pretty straightforward once I had your base to work from.

Cheers

Andrew

• ###### 5. Re: Averaging a sum for a specific time period

thank you for saving me!!!! I was able to follow the example and apply it to my data.  Love you guys, super happy