2 Replies Latest reply on Oct 15, 2018 1:39 PM by Mavis Liu

    Calculating monthly daily averages of products but for the first month only count days from first date

    Alex Martino

      Hello. I have a spreadsheet attached with the first tab called "correct". It has the daily averages by month of different products.

       

      What I would like to do is in the first month of each product, to only take the daily average from the first data point in the month (otherwise it divides it by 30 days, even if the product has only been active for 5). All the other months can be normal.

       

      For example, for slouchy hoodies at the bottom, the first month of data-September-is understated because it's being divided by 30 days.

       

      Additionally, I would like to calculate the yearly daily average and I would like to accommodate for the same thing (divided by days only at the starting point of each product).

       

       

      Thanks!!

        • 2. Re: Calculating monthly daily averages of products but for the first month only count days from first date
          Mavis Liu

          Hi Alex,

           

          First I created a calculated field finding the minimum date per category:

           

          Min Date per Category

          {FIXED [Category]:

          min(

          if [Sales Volume] >=1 then [Date]

          END)}

           

          The below counts the distinct number of days which have a sales volume of one or over

          Count of Days not null

          COUNTD( if { FIXED [Category], [Date]: SUM([Sales Volume])} >=1

          then [Date]

          END)

           

          The below works out the average you want

          Average first day of the month

          if MIN(MONTH([Date])) = min(month([Min Date per Category]))

          then

          sum([Sales Volume])/[Count of Days not null]

          ELSE

          [Average Sales Units]

          END

           

          For slouchie the sum of sales for september (where the days is 1 or over) is 24. There are 10 days in september where the sum of volumes is 1 of over, so the daily average for september is 2.4.

           

          For the annual daily average, I have the below calculation:

           

          Count of Days not null - annual

          sum({ FIXED [Category]:  COUNTD(  if { FIXED [Category], [Date]: SUM([Sales Volume])} >=1

          then [Date]

          END)})

           

          Then I have the annual daily average:

           

          Annual Daily Average

          SUM( { EXCLUDE MONTH([Date]): sum( { FIXED [Category]: SUM([Sales Volume])})})/[Count of Days not null - annual]

           

          So for the annual daily average, the sum of sales volume for Slouchie is 34, the number of days in the year where the volume is 1 or over is 12. So 34/12 = 2.83.

           

          My calculations are included in the worksheet called correct (2):

           

          2018-10-15_21h37_46.png

           

          Are these the results you're looking for?

           

          Thanks,

           

          Mavis