1 2 Previous Next 19 Replies Latest reply on May 24, 2013 2:34 PM by John Mogielnicki

# Calculation on Avg sales per week in a month

Hi Team,

I have a requirement wherein i need to calculate average sales per week for a month if i select month view, Similary i need to show averages sales per day for a week if i select weekly view.

Please let me know how can this be achieved. It would be great if i get a code with screenshots.

Thanks

Mohan

• ###### 1. Re: Calculation on Avg sales per week in a month

I have this same need Mohan!  Is anyone able to help us?  I have attached a workbook in which I have a calculation for the average views per day.  The reason I am able to do this is because my data is broken out so that each day is one line, so when I compute the average it computes by day.  But if I want to compute the average views per week I don't know how to do this.  Please help!

• ###### 2. Re: Calculation on Avg sales per week in a month

How do you want to handle partial weeks?

--Shawn

• ###### 3. Re: Calculation on Avg sales per week in a month

That is an excellent question Shawn.  Maybe I would want to know this information by year instead of month, so that this wouldn't be an issue.  Or I could have the user choose a 4 week period, with no partial weeks.

I'm also interested in the theory behind this, because I have run up against this issue a couple times in different ways.  I think the general way to state the question is: how does one take an average of a value across one dimension (in the example above, years), but split out along a different dimension (in the example above, weeks).

Thanks,

John

• ###### 4. Re: Calculation on Avg sales per week in a month

Actually John I think I (and maybe you) are over thinking this. Seeing how your viz is set up wouldn't it just be:

AVG([Views])*7

If you're getting the average daily views by month in that first tab, don't you just take that times seven days. That is assuming the first sheet is the daily number you're looking for.

--Shawn

EDIT: I attached the workbook.

Message was edited by: Shawn Wallwork

• ###### 5. Re: Calculation on Avg sales per week in a month

Hey Shawn,

That's an interesting/ingenious solution, and in the mockup data I put together it works because each line is one day of activity.  But in my actual data that is not the case.  I have many lines per day.  In that case, is there another way to get to the same result?

Thanks,

John

• ###### 6. Re: Calculation on Avg sales per week in a month

John, while I haven't mastered Table Calculations I've started to get a feel for when one is required, and I'm fairly certain that is the case here. It's going to be one of those addressing/partitioning problems to get the aggregation you're looking for. So time to call in the big gun Brad Llewellyn or Joshua Milligan . If you have a chance it would be best for them if you posted a workbook that includes multi-rows per day, so they don't post same dumb solution I did.

--Shawn

• ###### 7. Re: Calculation on Avg sales per week in a month

Mohan and John,

In my attempt to answer both of your questions simultaneously, I created the attached workbook.  A few things to note:

You can calculate the daily average for each week, weekly average for each month, and monthly average for each year using the parameter.

In order to create a relationship between weeks and months, I had to create a 4-4-5 fiscal calendar.

I also copied the mock-up data set so that there are two rows corresponding to each day

The first three sheets are John's work with his mock-up.  The final two sheets are my work with my data.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 8. Re: Calculation on Avg sales per week in a month

Brad, first thanks for taking this on. [Please let me know if I ever ping you too often; don't want to abuse you willingness to contribute.]

Clarification: You said you had to 'create a 4-4-5 fiscal calendar'. It seems you did this in the data you prepared/generated before bringing it into Tableau. Am I understanding this correctly?

Thanks,

--Shawn

• ###### 9. Re: Calculation on Avg sales per week in a month

Shawn,

I'm more than willing to help in cases where the users can't seem to find an answer.  And to you second question: yes I did the calendar in Excel.

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 10. Re: Calculation on Avg sales per week in a month

Brad, I'm going to expose my ignorance here, is there a way to create the 4-4-5 calendar inside tableau? This is an issue that comes up quite often on these forums. Your thoughts/recommendations will be greatly valued.

--Shawn

• ###### 11. Re: Calculation on Avg sales per week in a month

Shawn,

You could create with a very ugly number of CASE statements and calculated fields, or you could blend it in.  Check this out.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 12. Re: Calculation on Avg sales per week in a month

Brad, is that something you could turn into a doc for the Calculation Reference Library?

Jonathan

• ###### 13. Re: Calculation on Avg sales per week in a month

Jonathan,

Which workbook?  The Average Views or the Fiscal Calendar?

Thanks,

Associate Consultant

Mariner, LLC