Hi, I am trying to get an average of sales per week for each person. My dimension is Name.
My problem is that if I filter for December 2011 and Bob only had sales for 2 out of the 4 weeks in December then the formula only uses those 2 weeks he had data for. I want the expression to take his total sales for the month of the December and provide me a weekly average regardless how many weeks he actually submitted a sale during the month of December.
Even though your business weeks start on Fridays, it may be worth considering calculating the amount of weeks in a Month not based on Fridays.
For example, look at April 2011, we get 5 Fridays / business weeks with 21 working days (assuming Mon-Fri).
For May 2011, we get only 4 Fridays, but with 22 working days. Even if you count all days of a month as working days, you will get 5 weeks / 30 days compared to 4 weeks / 31 days, right?
And all Months show either 4 or 5 Fridays, that means you divide your monthly sales either by 4 or 5, that is 25% difference, not taking into account that most month show 21 / 22 working days, which is a much smaller variation.
This will of course also affect your comparison of average weekly Sales.
So you might want to consider something like:
LOAD only(Month) as Month
,NetWorkDays(Month,MonthEnd(Month)) as NumWorkDays
,NetWorkDays(Month,MonthEnd(Month)) / 5 as NumWorkWeeks
Resident Calendar group by Month;
And use sum(NumWorkWeeks) to divide your count(distinct SaleId).
If you work all days in a month, it could look like: