5 Replies Latest reply on Jan 15, 2019 1:09 PM by Ken Flerlage

# Calculate weeks within a given month & year to understand units per week

Hello,

I seem to be having some difficulty. I have a data set that pulls data by week. I am looking to show it in a visualization that filters by month (a PIE chart that shows average by month) so Tableau takes the weekly data and rolls it up for the month but I need it to still present the data as an average units per week. My data set has the years 2017, 2018, and 2019. I was using the below formula but I think it is dividing by all the weeks in the month for every year present. e.g. Dec 2018 (4 Weeks) and Dec 2017 (4 Weeks) it divides by 8, not 4? My values coming out are very small so that is what I am assuming it is doing.

Here is the formula:

([Units per Week])/{FIXED year([Reporting Week]):COUNTD(DATEPART('week',[Reporting Week]))}

Cheers

• ###### 1. Re: Calculate weeks within a given month & year to understand units per week

These kinds of issues can be difficult to troubleshoot without seeing how the data is structured. Would you be able to provide a sample packaged workbook for us to work with?

• ###### 2. Re: Calculate weeks within a given month & year to understand units per week

For example, the PIE chart should be showing for January 2017 in Ontario that the average Units per Purchase of Blue Shirt is 2 and Red Shirts is 3.2 (Sum of Units per Purchase/# of weeks in January 2017 (5). Make sense?

• ###### 3. Re: Calculate weeks within a given month & year to understand units per week

The problem appears to be related to the Tableau Order of Operations (see Tableau's Order of Operations - Tableau)

You have three dimension filters which you're trying to apply to the view, but FIXED LODs occur before dimension filters, so the LOD is being calculated first, then the filters are being applied. I think you want to apply the filters first then the LOD. To do that, right click on each of the filters and choose "Add to Context". That will make them context filters which will apply before the LOD calculation and should give you the correct results.

• ###### 4. Re: Calculate weeks within a given month & year to understand units per week

Thanks so much! This graphic is very helpful!

• ###### 5. Re: Calculate weeks within a given month & year to understand units per week

Any time!