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.

             

            Thanks,

             

            Mavis

            • 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