3 Replies Latest reply on Oct 15, 2018 12:50 PM by Mavis Liu

    Fixed Formula

    Alex Martino

      How does a fixed formula work with date?


      I have products and monthly dates and this formula to get the average daily amount by month


      AVG({ FIXED [Category], [Date]: SUM([Sales Volume])})


      When I do month[date] above it changes the numbers so that they are now sums of the month instead of the average daily amount. Why is this?

        • 1. Re: Fixed Formula
          Mavis Liu

          Hi Alex,


          When you use AVG({ FIXED [Category], [Date]: SUM([Sales Volume])}) it will fix it against the dimension of category and the whole date itself. So this date is taking into account day, month and year- and possibly the time as well if it's a date time field.


          So the above is finding the sum of the sales volume for every single category and date, then finding the average. (I am using UK format dates)



          Category   Date                       Sales Volume

          A              01/03/2017              1

          A              02/03/2017              2

          B              01/04/2017              3

          B              05/04/2017              6


          If you do AVG({ FIXED [Category], [Date]: SUM([Sales Volume])}) the result will be 1+2+3+6 divided by 4, which is 3.


          If you do AVG({ FIXED [Category], Month[Date]: SUM([Sales Volume])})


          It will actually be:


          Category   Date                       Sales Volume

          A              March                      3

          B              April                        9


          Which comes out to be 12/2 = 6



          Using Month(date) just means it's finding the datepart of the date.





          1 of 1 people found this helpful
          • 2. Re: Fixed Formula
            Alex Martino

            But if you are fixing it to category, won't it just return the value at the category level, not the overall level?


            For example, March for Product A will be 3 divided by 30 days. April for Product B will be 9 divided by 90 days.


            This is what I see in my spreadsheet.

            • 3. Re: Fixed Formula
              Mavis Liu

              Hi Alex,


              If you fix it to category level in the calculated field, so if I do a AVG({ FIXED [Category]: SUM([Sales Volume])}) then the results will be:


              Category   Date                       Sales Volume      FIXED

              A              01/03/2017              1                             3

              A              02/03/2017              2                             3

              B              01/04/2017              3                             9

              B              05/04/2017              6                             9


              in other words :


              Category  Fixed

              A                3

              B                9


              As what it's doing is summing it up per category, then finding the average. As category is in the view, finding the min/max/avg will give the same results.