4 Replies Latest reply: Jan 5, 2012 7:31 AM by Stefan Wühl RSS

    Weekly Average Problem

      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)


        • Weekly Average Problem
          Stefan Wühl

          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?

            • Re: Weekly Average Problem

              Yes, it is.  Friday is the official definition of my business week.  Friday-Thursday.

                • Re: Weekly Average Problem
                  Stefan Wühl

                  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:



                  LOAD *

                  ,day(Date) as DayOfMonth

                  ,weekday(Date) as Weekday

                  ,Date(monthstart(Date),'YYYY-MMM') as Month

                  ,if(WeekDay(Date)=4,Date) as FridayDate



                  date(makedate(2011,1,1)+recno()-1) as Date

                  AUTOGENERATE 365




                  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



                    • Re: Weekly Average Problem
                      Stefan Wühl

                      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;