topic Re: Turnover using AGGR in App Development

Turnover using AGGR

dvalenti — Fri, 02 Feb 2018 00:27:17 GMT

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])

)

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])

)

, [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

Re: Turnover using AGGR

marcus_sommer — Fri, 02 Feb 2018 09:44:21 GMT

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

dvalenti — Fri, 02 Feb 2018 11:55:49 GMT

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 — Sat, 03 Feb 2018 10:20:11 GMT

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

Re: Turnover using AGGR

dvalenti — Wed, 28 Feb 2018 19:42:59 GMT

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.