Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
gardan
Contributor III
Contributor III

aggr() function help

lets say I have the following data set:

ShipmentIDFiscalWeek
500313
500413
501114
501214
501314
501415
501515
501816
501916
502016
502317
502417


How would I be able to find the average number of shipments per week with one expression?

Furthermore, if I had another field in there, say . . StoreID the loads were shipped to, how would I find standard devation of loads per week sent to each store? I think all this can be done with the aggr() function and a nested aggregation, but I can't seem to get it to click.

Any help is appreciated. Thanks!!

3 Replies
wizardo
Creator III
Creator III

Hi,

maybe this?

avg(aggr( Count (ShipmentID),FiscalWeek))

the above will give you the first stage

then if you take the entire expression into another aggr function like this:

stdev( aggr( avg(aggr( Count (ShipmentID),FiscalWeek)),StoreID))

this will give you the second stage

gardan
Contributor III
Contributor III
Author

Thanks for the relpy!

I was trying that, but it does not seem to work. I thought I might just be using it in the wrong context so I made a simple text object with the above average shipment per week expression in it. It did not seem to work there either.

I must be missing something.

Thanks!

gardan
Contributor III
Contributor III
Author

oh, wait . . it seems to be working now . . how odd.

Thanks for the help!