Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
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.
Can you post a sample with small data?
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,
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.
This worked. Thank you very much.