4 Replies Latest reply on Jan 17, 2014 8:37 AM by Joshua Milligan

# Average number of records by day of week

I thought I was pretty knowledgeable about Tableau but I've run into a brick wall on this one and haven't been able to find any solution in my web searching here and elsewhere.  Part of the problem is that it's difficult to describe.

Let's say I have a month's worth of data where each record is tagged with the date.  I want to know the average number of records for each day of the week.  For example, in a given month there may be four Mondays with record volumes of 20, 32, 45 and 33 for each of the four weeks or 130 total. The average volume on a Monday then is 130/4 or 32.5.  The attached workbook gives a simplified example of the data and the expected solution.

I imagine I need to use an aggregate function or two but as of yet I haven't been able to get this to work.  Any suggestions?

Thanks!

• ###### 1. Re: Average number of records by day of week

Hi, I would guess you need a calc field on the date column,that has this formula DATENAME('weekday',[Date col]). And then on the worksheet you can drag and drop the calc field and the avg of number of records.

• ###### 2. Re: Average number of records by day of week

Marc-Paul,

I've shown a possible way of getting the answer in the attached workbook.  It uses a couple of table calculations to do a a WINDOW_AVG along each week at partitioned by each weekday.  That means that I need Week in the view, but I don't necessarily want it to show.

Here's the basic idea:

The first view shows the setup and the calculations.  I only really need to show the first row for each weekday because the average is calculated for every Week -- Really, for performance I should re-write the code to be IF FIRST() == 0 THEN WINDOW_AVG(SUM(1)) END.

Hopefully that helps!

Regards,

Joshua

• ###### 3. Re: Average number of records by day of week

Thanks, Joshua!  That certainly is the right answer although now I don't quite understand how it works.  Thanks for pointing the way, however.

• ###### 4. Re: Average number of records by day of week

Marc-Paul,

You're welcome!  I definitely would be glad to explain anything that doesn't make sense.  Feel free to reach out to me with any questions.

Regards,

Joshua