5 Replies Latest reply: Jan 28, 2014 5:35 PM by Burcu Agar

# Using STDEV and AVG with AGGR

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

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

Can you post a sample with small data?

• ###### Re: Using STDEV and AVG with AGGR

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

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

This worked. Thank you very much.