Announcements
cancel
Showing results for
Did you mean:
Not applicable

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,

1 Solution

Accepted Solutions
MVP

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.

5 Replies
MVP

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.

Not applicable
Author

Can you post a sample with small data?

Not applicable
Author

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,

MVP

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.

Not applicable
Author

This worked. Thank you very much.

Community Browser