3 Replies Latest reply on Jul 24, 2018 5:09 AM by Naveen B

    understanding weekly average calculation

    Alex Martino

      Hello. Can someone explain how this calculation works? What does the ":" signify? Why are their two fixed portions?


      {FIXED DATETRUNC('week',[Order Date]):AVG({FIXED [Order Date]:COUNTD([Order ID])})}

        • 1. Re: understanding weekly average calculation
          meenu choudhary

          Hi Alex,


          Above is an example of nested LOD. we can split it into two parts for understanding:

          1. {FIXED [Order Date]:COUNTD([Order ID])}


          it is counting the distinct number of orders for each date ,so we can name it as order_num.


          2. {FIXED DATETRUNC('week',[Order Date]):AVG(order_num)}


          this one is calculating the avg order_num per week.


          • 2. Re: understanding weekly average calculation
            Mavis Liu

            Hi Alex,


            The : is for any LOD calculation, before : you are deciding which dimension you are fixing to. Then after the : is the aggregation.


            In this calculation, it is working out for every order date, it is doing a distinct count of the Order ID. Then within the week, it is averaging the distinct count of order IDs per order date.





            • 3. Re: understanding weekly average calculation
              Naveen B

              Hi Alex,


              {FIXED DATETRUNC('week',[Order Date]):AVG({FIXED [Order Date]:COUNTD([Order ID])})}


              Lets breakdown calculation to steps


              1) {FIXED [Order Date]:COUNTD([Order ID])}


              It will count the number of orders for each and every order date like below


              2) DATETRUNC('week',[Order Date])


              this calculation will give the week start date for whole week



              3) {FIXED DATETRUNC('week',[Order Date]):AVG({FIXED [Order Date]:COUNTD([Order ID])})}

              every week it will calculate the average


              (FIXED <Dimension> : <Computation>}


              Fixing the dimension and computing the computation on the dimension


              Hope this helps kindly mark this answer as helpful/correct so that it will help others

              1 of 1 people found this helpful