Turnover using AGGR

[dvalenti]

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

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

[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

[dvalenti]

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.

[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

[dvalenti]

Hello Marcus,

I can't use the dimensionality function because my columns can change and it can be like Product Group, Store or others. That's the reason I can't put the dimensions in the AGGR and can't use Dimensionality.