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

    Natasshia Lee



      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]))}


      Any insight would be helpful.