topic Re: AGGR() in QlikView

AGGR()

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

hello everyone,

i have below table, i want to used AGGR function to calculate average.

i am not getting why both o/p shows differ value if they calculate average.

it become difficult for me to find a difference between them.

=avg(aggr(sum(sales),prod)) =avg(sales)

why it shows different o/p if they calculate average ?

please let me know

thank you..

Re: AGGR()

tresB — Fri, 15 Sep 2017 10:29:59 GMT

Read this blog: Average – Which average? It would be worth reading I guess.

Re: AGGR()

arvind_patil — Fri, 15 Sep 2017 10:35:47 GMT

Hi Prachi ,

try below One :

=Aggr(Avg(sales),prod)

Thanks,

Arvind Patil

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 10:40:41 GMT

=Sum( sales) / Count( distinct prod) it shows 365

but when i used code instead of prod then it show 304.16

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 10:45:54 GMT

it shows o/p when i clicked on field otherwise it shows blank

Re: AGGR()

effinty2112 — Fri, 15 Sep 2017 10:48:25 GMT

Hi Prachi,

Your total sales = 3650.

avg(sales) = your average per line = 3650 / 12 = 304.16666

avg(aggr(sum(sales),prod)) will give the average per prod = 3650 / 10 = 365.

Regards

Andrew

Re: AGGR()

rohitraut — Fri, 15 Sep 2017 10:53:15 GMT

Hello Prachi,

Try this,

=avg(aggr(sum(sales),prod,code))

Re: AGGR()

its_anandrjs — Fri, 15 Sep 2017 10:54:18 GMT

Your Avg(Sales) and avg(aggr(sum(sales),prod)) both are different first go for the AVG calculation and check the result

1. Avg(Sales) give different result try to put this all expression in the chart and see the result what they return back.

2. avg(aggr(sum(sales),prod)) is also different it is by Prod and if you use

avg(aggr(sum(sales),prod,code))

avg(aggr(sum(sales),code))

This all are also different so be aware about the AVG usage and output also.

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 11:00:16 GMT

ok but in the following exp why it doesn't take average per prod ,it shows 304.16 o/p

= Avg( Aggr( Sum(sales), prod,code ) )

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 11:01:55 GMT

but how i know which is correct or not

Re: AGGR()

Lucke_Hallbergson — Fri, 15 Sep 2017 11:05:41 GMT

You have 12 sales and 3650 in total sales = 304,16667 in average sales per deal.

You have 10 unique products sold and 3650 in total sales, average sale per unique product = 365

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 11:10:08 GMT

ok but tell me one thing in below expression why its not taking unique records

,it shows 304.16 o/p

= Avg( Aggr( Sum(sales), prod,code ) )

Re: AGGR()

Lucke_Hallbergson — Fri, 15 Sep 2017 11:12:58 GMT

If You want the average per sale when using aggr =avg(aggr(avg(sales),prod, code)) will get the average sale per sale (not just product) eg 304,16667 if that is what You want.

Re: AGGR()

Lucke_Hallbergson — Fri, 15 Sep 2017 11:14:35 GMT

Re: AGGR()

Lucke_Hallbergson — Fri, 15 Sep 2017 11:18:22 GMT

Added one column "=avg(aggr(sum(sales),prod))" (the one you started with) for clarity(?!?) 😉

Re: AGGR()

Anonymous — Fri, 15 Sep 2017 11:30:20 GMT

Thanks

nice explanaion, very helpful to me