topic Re: Aggregation in QlikView

Aggregation

danielle_v — Tue, 25 Jun 2013 14:10:17 GMT

Hi All,

I'm trying to do a calculation in a chart expression, but I'm not sure that my data is calculating at the correct level so I think I may need to aggregate it somehow.

My dataset is based around orders, and there can be multiple items per order, so my data is at Order-Item level.

However, I need to calculate the average number of DaysToDeliver at Order level. I think Qlikview is automatically calculating this at Order-Item level

for example;

Order Item DaysToDeliver

1 A 50

1 B 50

1 C 50

2 R 10

2 S 10

3 Z 3

I would want the avg(DaysToDeliver) to give me a result of 21 (the average of 10, 50 and 3)

However, it is actually giving me a result of 28.8333 (the average of 50, 50, 50, 10, 10, 3)

I have experimented with the aggr function, but can't seem to get it to return any results so I'm not sure this is even the way I need to go!

Any help much appreciated!

Re: Aggregation

Anonymous — Tue, 25 Jun 2013 14:14:01 GMT

The example in this post may help you. You need to use the aggr() command, as done in the right hand table.

http://community.qlik.com/message/360505#360505

Jonathan

Re: Aggregation

Gysbert_Wassenaar — Tue, 25 Jun 2013 14:15:49 GMT

Try avg(aggr(only(DaysToDeliver),Order))

Re: Aggregation

danielle_v — Tue, 25 Jun 2013 14:33:34 GMT

Hi Gysbert,

Many thanks for this, I have used your calculation and it is now returning a result.

However, i forgot to mention in my original post, I need to calculate 'DaysToDeliver' in my expression too (DeliveryDate - OrderDate)

I have therefore amended your expression as follows;

avg(aggr(max(DeliveryDate - OrderDate), Order))

I hope this is a valid thing to do? It still seems to return a result, so I hope so!

Thanks again for your help!

Re: Aggregation

daveamz — Tue, 25 Jun 2013 14:42:24 GMT

Hello Danielle,

Use avg(distinct DaysToDeliver) to calculate the average of distinct values (10,50,3)

Regards,

David