3 Replies Latest reply: Feb 3, 2018 5:20 AM by Marcus Sommer RSS

    Turnover using AGGR

    Daniel Valenti

      Hello all,

       

      I'm trying to generate an product turnover KPI, It must be calculated per week so it is: week sales quantity divided by the average ((initial+final)/2) supply of the week. The formula is:

       

      Sum({<Table={'Sales'}>}Quantity)

      /

      (

           (

                Sum({<Table={'Supply'}>}Quantity)

                +

                Sum({<Table={'Supply'}>}[Initial Quantity])

           )

           /2

      )

       

      And the month turnover is the sum of the weeks turnover, than I did:

       

      Sum(

           Aggr(

                Sum({<Table={'Sales'}>}Quantity)

                /

                (

                     (

                          Sum({<Table={'Supply'}>}Quantity)

                          +

                          Sum({<Table={'Supply'}>}[Initial Quantity])

                     )

                     /2

                )

           , [Week])

      )

       

      But when I use this on a table, just one line shows the value and the others are 0. Like image below

      Turnover.JPG

      I can't put "Departamento" in the AGGR because the turnover will be used with other dimensions. Does someone know how to do it?

       

      thanks in advance.

       

      Daniel

        • Re: Turnover using AGGR
          Marcus Sommer

          I think you need further dimensions within your aggr and would assume at least Product else the calculation would be globally unless the Week and I don't believe that this could be wanted for your KPI.

           

          - Marcus

            • Re: Turnover using AGGR
              Daniel Valenti

              Hello Marcus,

               

              I can't put Product in my AGGR because the sum of the products turnover is different of the turnover of the sum. for example:

               

              Product     Sales     AVG Supply     Turnover

              A               10           100                    0,10

              B               50           200                    0,25

              Total          60          300                     0,20

               

              The turnover must be 60/300 -> 0,2 and if you sum the turnovers it will be 0,35.

                • Re: Turnover using AGGR
                  Marcus Sommer

                  In this case I'm not sure if you really need the aggr() and if they should be applied over the whole calculation or just parts of it. Nevertheless you could try your approach by extending the aggr with a NODISTINCT statement.

                   

                  Personally I think you need a TOTAL and would try something like this:

                   

                  if(dimensionality() = 0,

                       Sum({<Table={'Sales'}>} TOTAL Quantity) /

                       Sum({<Table={'Supply'}>} TOTAL rangesum(Quantity, [Initial Quantity]) / 2),

                       Sum({<Table={'Sales'}>}Quantity) /

                       Sum({<Table={'Supply'}>} rangesum(Quantity, [Initial Quantity]) / 2)

                  )

                   

                  - Marcus