Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dvalenti
Partner - Contributor II
Partner - 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

4 Replies
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
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

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.