Using STDEV and AVG with AGGR

[#1]

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,

[tresesco]

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.

[#3]

Can you post a sample with small data?

[#4]

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,

[Nicole-Smith · Accepted solution]

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.

[#6]

This worked. Thank you very much.