Aggregation

[danielle_v]

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!

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

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

[danielle_v]

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!

[daveamz]

Hello Danielle,

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

Regards,

David