5 Replies Latest reply: Aug 29, 2013 10:17 AM by Giedre Slivko RSS

    How to calculate number of days including excluded days

      I have sales data for two years. I have to do analytics on average sales based on cyclic calendar group which consists of all possible calendar dimension - year, quarter, month, year-month, decade, day of week and so on.

      So my formula of average sales should look like

      SUM(sales) / COUNT(TOTAL calendar.date). E.x. If my cyclic group is turned to "Decade" (10 days not years), formula should be SUM(sales) / 10 for each decade.

      Unfortunately, I have dates, where there were no sales. And my formula counts SUM(sales) / 6. Or if my cyclic group is turned to "Month", formula gives me SUM(sales) / 21.

       

      The concept of days count where counting is based on subtraction of MIN MAX dates isn't doing any good because I can choose April in 2012 and 2013 years. So MIN MAX day count starts at 2012 04 01 and ends at 2013 04 31 = 395 days. And what I want is 60. Or if I choose decade, day count in one decade in two years should be 20.