topic Re: 3 Month rolling Std dev in QlikView

3 Month rolling Std dev

Thu, 08 May 2014 20:53:45 GMT

I am new to qlikview. I'm having an issue trying to find the 3 month rolling std dev of my data. In my attached file, i have a straight table set up doing simple sum calculations and the std dev for the month. The last column, "3 month Std Dev", needs to the the std dev of the current month, the prior month and the prior prior month. example. "3 month Std Dev" of May - 2011 data will do the std dev of May, Apr, and Mar 2011 combined. I want to the values to appear in the straight table at all times no matter the selection and has to change based on the users selection of a location

Re: 3 Month rolling Std dev

Thu, 08 May 2014 21:43:05 GMT

Try this

rangestdev (above(Stdev(ErrorTime/OperatingTime),0,3))

Re: 3 Month rolling Std dev

Thu, 08 May 2014 21:43:38 GMT

You can try

RangeStdev(Above(Stdev(ErrorTime/OperatingTime),0,3))

Re: 3 Month rolling Std dev

Fri, 09 May 2014 17:24:18 GMT

This way doesn’t work cause it takes the stdev of the already calculated stdev values. Which is not right. I need a way of grabing all of the 3 months data as a whole then do the stdev calculation

Re: 3 Month rolling Std dev

Gysbert_Wassenaar — Fri, 09 May 2014 17:32:36 GMT

Perhaps this document helps you get the result you're looking for: Calculating rolling n-period totals, averages or other aggregations

Re: 3 Month rolling Std dev

Fri, 09 May 2014 19:13:30 GMT

The Range aggr combination works right for sums, but not for avgs and stdevs. The AsOf Table trick does work, but when you have 2 million records and you are trying to do a 3 month rolling avg/stdev or even a 12 month, the table grows very large

Re: 3 Month rolling Std dev

Gysbert_Wassenaar — Fri, 09 May 2014 19:18:19 GMT

You only need the months in the AsOf table, not any fields from the fact table. The AsOf table will be associated with your MasterCalendar. So if you have 100 years you will have 1200 months and that translates to 3600 records in the AsOf table for 3-month rolling calculations. That doesn't sound very large to me. 🙂