topic Using STDEV and AVG with AGGR in QlikView

Using STDEV and AVG with AGGR

Tue, 28 Jan 2014 05:30:44 GMT

I have a table with item numbers, date and demand for those items, from which I am trying to calculate average and standard deviation of monthly demand.

I loaded the table from MySql and created "MonthYear" with MonthName in the script.

I used item number as dimension and avg(aggr(sum(SalesQty),MonthYear))and Stdev(aggr(sum(SalesQty),MonthYear)) for expressions . Both of them result in null value.

I was able to calculate monthly average with this formula: sum(SalesQty)/Count(distinct(MonthYear)) however, I couldn't get around the stdev calculation. How can I formulate the calculation of std dev of sum of monthly demand in qlikview?

Any help is appreciated.

Thank you,

Re: Using STDEV and AVG with AGGR

tresB — Tue, 28 Jan 2014 05:39:53 GMT

I guess the order of functions are not right. Could you post a sample qvw explaining expected result? That's the fastest way of getting answered.

Re: Using STDEV and AVG with AGGR

Tue, 28 Jan 2014 08:16:43 GMT

Can you post a sample with small data?

Re: Using STDEV and AVG with AGGR

Tue, 28 Jan 2014 19:05:37 GMT

Thank you for your quick responses. Here is a sample. I am trying to calculate monthly average demand by ItemID. I did it by Avg Demand: sum(salesqty)/[active months].

Average w Aggrgate: avg(aggr(sum(SalesQty),MonthYear)) does not work.

Std Dev of Demand: Stdev(aggr(sum(SalesQty),MonthYear)) does not work either. I want to calculate the deviation of the monthly sums.

Thank you,

Re: Re: Using STDEV and AVG with AGGR

Nicole-Smith — Tue, 28 Jan 2014 19:12:39 GMT

I believe you also need to add ItemID as a dimension in your aggr() expressions:

avg(aggr(sum(SalesQty),ItemID,MonthYear))

and

stdev(aggr(sum(SalesQty),ItemID,MonthYear))

Your file updated with these changes is also attached. It's definitely working for the average. You'll have to figure out if it's working properly for the standard deviation.

Re: Using STDEV and AVG with AGGR

Tue, 28 Jan 2014 22:35:55 GMT

This worked. Thank you very much.