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: 
danielle_v
Creator
Creator

Aggregation

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Anonymous
Not applicable

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

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


talk is cheap, supply exceeds demand
danielle_v
Creator
Creator
Author

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
Partner - Creator III
Partner - Creator III

Hello Danielle,

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

Regards,

David