topic Re: KPI based on Aggr() function in QlikView

KPI based on Aggr() function

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I have a pivot chart to display number of sales and number of returns of items by business unit, store, and by return reason. One item can be associated with several return reasons.

There are two KPI in the chart: 1) number of item returns and 2) number of item sales. I use Aggr() function to calculate item sales. Below is an example of the chart:

Business unit	Store	Item	Return reason	Nb sales	Nb returns
100	719	A	Broken	23	1
			Change for another item	23	1
			Wrong size	23	1
100	83	A	Wrong size	15	3
			Broken	15	2

The "Nb sales" has to be a total per item. Return reason should not be taken into account in this case.

If item A was sold 23 times in store 719 and returned 3 times then '23' should be displayed on each line (as the example above).

Here is the expression for 'Nb sales':

Sum(Aggr(Sum([Quantity sold]),[Store],[Item]))

The only problem is that value '23' is displayed only one time (on one of the return reason lines). How can it be repeated on each return reason line ?

Thank you in advance,

Elina

Re: KPI based on Aggr() function

MK_QSL — Fri, 23 Mar 2018 09:43:32 GMT

Try

Sum(Total <Item,Store> [Quantity sold])

Re: KPI based on Aggr() function

MK_QSL — Fri, 23 Mar 2018 09:44:59 GMT

or use

Sum(Aggr(NoDistinct Sum([Quantity sold]),[Store],[Item]))

Re: KPI based on Aggr() function

edemerdjieva — Fri, 23 Mar 2018 09:54:16 GMT

Doesn't working. NODISTINCT doubles the result

Re: KPI based on Aggr() function

oussema_laribi — Fri, 23 Mar 2018 09:54:27 GMT

Try This :

Sum(Total <"business unit" ,Store,Item> [Quantity sold])

Re: KPI based on Aggr() function

MK_QSL — Fri, 23 Mar 2018 09:56:46 GMT

have you tried my 1st answer?

Yeah.. add Business Unit also in Total Qualifier

Sum(Total <Item,Store, [Business Unit]> [Quantity sold])

Sum(Aggr(NoDistinct Sum([Quantity sold]),[Store],[Item],[Business Unit]))

Check spelling of all fields

Re: KPI based on Aggr() function

edemerdjieva — Fri, 23 Mar 2018 09:57:00 GMT

It would work if the dimension 'return reason' wasn't included in the chart.

Without the Aggr() function the expression is always = 0.

Re: KPI based on Aggr() function

edemerdjieva — Fri, 23 Mar 2018 10:08:47 GMT

Ok, now it worked. I had to add all dimensions in the chart to the TOTAL qualifier.

Here is the final expression:

Sum(TOTAL <[Business Unit], Store, Item> Aggr(Sum([Quantity sold]),[Business Unit], [Store], [Item]))

Thanks for the help!

Re: KPI based on Aggr() function

MK_QSL — Fri, 23 Mar 2018 10:14:38 GMT

Glad that you got your output.

Sum(Aggr(TOTAL <[Business Unit], Store, Item> Sum([Quantity sold]),[Business Unit], [Store], [Item]))

Have you tried this?