topic Re: Standard deviation with aggregate set analysis in QlikView

Standard deviation with aggregate set analysis

jarrad_murray — Thu, 04 Aug 2016 10:24:44 GMT

Hi All,

I am trying to apply set analysis to a standard deviation equation which uses an aggregate function. I am having real difficulty getting this to work correctly and would greatly appreciate and assistance or guidance.

The equation that I am using is

Stdev(aggr(sum({<PODGroup4Description = {'AandE'}, MonthKey = {'<=$(vMonthPosition)'}>}ActivityActual), MonthKey, ActivityActual))

The variable $(vMonthPosition) = 13 but will be calculated within the application to allow it to be dynamic.

This equation gives me a figure of 262 for the standard deviation instead of the 108 I am expecting.

Looking at the comments about the aggregate function I need to apply the set analysis within the outside of the aggregate but when I do this it errors and gives me nothing.

Does anyone have an idea as to where I am going wrong with this or what I might be able to do to fix it.

Kind Regards

Re: Standard deviation with aggregate set analysis

Gysbert_Wassenaar — Thu, 04 Aug 2016 12:38:18 GMT

You're doing two completely different aggregations. One over only MonthYear and the other over MonthYear and ActivityActual. If you aggregate both over MonthYear and ActivityActual or over MonthKey and ActivityActual or over only MonthKey or only over MonthYear then you'll get the same results.

Re: Standard deviation with aggregate set analysis

Frank_Hartmann — Thu, 04 Aug 2016 13:53:49 GMT

I think you should use a mastercalendar! then its really easy to filter the daterange.

See attached sample qvw.

hope this helps!

Re: Standard deviation with aggregate set analysis

jarrad_murray — Thu, 04 Aug 2016 14:33:45 GMT

That's fix it.

Thank you very much.