Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

dvalenti
New Contributor II

Turnover using AGGR

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

Tags (2)
4 Replies

Re: Turnover using AGGR

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
New Contributor II

Re: Turnover using AGGR

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

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
New Contributor II

Re: Turnover using AGGR

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.

Community Browser