topic Re: Static Threshold (Maintain static std deviation) in QlikView

Static Threshold (Maintain static std deviation)

Fri, 10 Mar 2017 18:39:41 GMT

Hi Qlik Community,

Given below data, I'm looking to create the Std Deviation column in the front end and for comparison purposes it should maintains a somewhat constant std deviation for part of the year and update the calculation to next previous 12 months. For instance, Month Year Jan 2016 I was looking at the std deviation from Jan-2015 to Dec 2015. I maintain this threshold until jun-2016. For July 2016, I update my periods to look at jul-2015 to jun 2016. I hold this calculation until the end of the year. I continue this pattern for Jan-2017, when i look at the std deviations for jan-2016 to dec 2016. Hopefully I was able to communicate my ask. Any help is appreciated.

Thanks

Month Year	Count	Std Dev
Jan-2015	45
Feb-2016	39
Mar-2016	21
Apr-2016	64
May-2016	43
Jun-2016	22
Jul-2016	65
Aug-2016	44
Sep-2016	41
Oct-2016	54
Nov-2016	48
Dec-2016	25
Jan-2016	49	STDEV.S($B$2:$B$13)
Feb-2016	39	STDEV.S($B$2:$B$13)
Mar-2016	55	STDEV.S($B$2:$B$13)
Apr-2016	64	STDEV.S($B$2:$B$13)
May-2016	43	STDEV.S($B$2:$B$13)
Jun-2016	22	STDEV.S($B$2:$B$13)
Jul-2016	11	STDEV.S($B$8:$B$19)
Aug-2016	35	STDEV.S($B$8:$B$19)
Sep-2016	41	STDEV.S($B$8:$B$19)
Oct-2016	44	STDEV.S($B$8:$B$19)
Nov-2016	48	STDEV.S($B$8:$B$19)
Dec-2016	25	STDEV.S($B$8:$B$19)

Re: Static Threshold (Maintain static std deviation)

sunny_talwar — Fri, 10 Mar 2017 18:54:22 GMT

This?

Re: Static Threshold (Maintain static std deviation)

Fri, 10 Mar 2017 19:12:48 GMT

Hi Sunny,

You're pretty close to the answer that i'm looking for. Please note that Jan-2016 std dev is calculating std dev using data from jan-2015 to dec 2015. It hold this # until Jun-2016, when we refresh the threshold. So for Jul-2016 i calculate std dev using data from jul-2015 to jun-2016. Hope thats clear. Please share your formula once you have the right number. Best,

Month Year	Count	Std Dev
Jan-2015	45
Feb-2015	39
Mar-2015	21
Apr-2015	64
May-2015	43
Jun-2015	22
Jul-2015	65
Aug-2015	44
Sep-2015	41
Oct-2015	54
Nov-2015	48
Dec-2015	25
Jan-2016	49	14.59425998
Feb-2016	39	14.59425998
Mar-2016	55	14.59425998
Apr-2016	64	14.59425998
May-2016	43	14.59425998
Jun-2016	22	14.59425998
Jul-2016	11	13.30157202
Aug-2016	35	13.30157202
Sep-2016	41	13.30157202
Oct-2016	44	13.30157202
Nov-2016	48	13.30157202
Dec-2016	25	13.30157202

Re: Static Threshold (Maintain static std deviation)

sunny_talwar — Fri, 10 Mar 2017 19:15:38 GMT

Here you go

If(Match(Num(Month(Only({1}MonthYear))), 1, 7) and RowNo() > 12, RangeStdev(Above(Sum({1}Count), 1, 12)), Above(Column(2)))