Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Any help will be greatly appreciated! Thanks!
count(distinct SaleId))/count(distinct Friday)
If any of the persons worked in each week of the month, you could try:
count(distinct SaleId))/count(total distinct Friday)
Probably not the best / most performant way of doing it, so maybe we want to calculate the amount of weeks differently, is amount of Fridays your "official" definition?
Yes, it is. Friday is the official definition of my business week. Friday-Thursday.
Since the amount of Fridays in a given month is static, I think it would be best to add the number of Fridays per Month in the data model, maybe along these lines:
Calendar:
LOAD *
,day(Date) as DayOfMonth
,weekday(Date) as Weekday
,Date(monthstart(Date),'YYYY-MMM') as Month
,if(WeekDay(Date)=4,Date) as FridayDate
;
LOAD
date(makedate(2011,1,1)+recno()-1) as Date
AUTOGENERATE 365
;
Fridays:
LOAD Month
,count(FridayDate) as NumFridays
Resident Calendar group by Month;
Then your expression could maybe look like:
=count(distinct SaleId) / sum(NumFridays)
edit: simplified Fridays table
One more thought:
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:
LOAD only(Month) as Month
,day(Monthend(Month)) as NumWorkDays
,day(Monthend(Month)) / 7 as NumWorkWeeks
Resident Calendar group by Month;