topic aggr() function help in QlikView

aggr() function help

gardan — Mon, 14 Dec 2009 23:10:58 GMT

lets say I have the following data set:

ShipmentID	FiscalWeek
5003	13
5004	13
5011	14
5012	14
5013	14
5014	15
5015	15
5018	16
5019	16
5020	16
5023	17
5024	17

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!!

aggr() function help

wizardo — Tue, 15 Dec 2009 02:17:00 GMT

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

aggr() function help

gardan — Tue, 15 Dec 2009 02:34:36 GMT

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!

aggr() function help

gardan — Tue, 15 Dec 2009 02:37:33 GMT

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

Thanks for the help!