topic Stdev calculation for rolling 12 Months in QlikView

Stdev calculation for rolling 12 Months

vinod_nallapaneni — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm facing an issue. I'm trying to find rolling 12 months standard deviation. I'm using expression as below but it won't work. PFA. sample data.

It would be appreciate if it is resolved.

Sqrt(pow(Sum({< MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM-YYYY'))"}>} Quantity)-

Avg({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM-YYYY'))"}>} Quantity),2)/

(Count({<MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM-YYYY'))"}>} Quantity))-1)

swuehl‌

Thanks in advance!

Vinod.

Re: Stdev calculation for rolling 12 Months

effinty2112 — Tue, 04 Jul 2017 12:37:30 GMT

Hi Vinod,

You should take your table and load it into QV in columar form using crosstable:

Directory;

CrossTable(Month, Measure)

LOAD [Product Code],

1*[42856] as [May 16],

1*[42826] as [Jun 16],

1*[42795] as [Jul 16],

1*[42767] as [Aug 16],

1*[42736] as [Sep 16],

1*[42705] as [Oct 16],

1*[42675] as [Nov 16],

1*[42644] as [Dec 16],

1*[42614] as [Jan 17],

1*[42583] as [Feb 17],

1*[42552] as [Mar 17],

1*[42522] as [Apr 17]

FROM

[Rolling Months STDEV.xlsx]

(ooxml, embedded labels, table is Sheet1);

The column headers were being read as numbers and the data was being read as dates so that's why the load statement might load a bit odd.

Then you can create this table:

Product Code	Stdev (Measure)
	184963.41
169365	2511.6433
169366	794.78802
195253	8838.7917
195304	28144.895
195308	1759.5949
195322	12091.516
237418	422.27432
365046	1477.3351
382196	54542.093
413043	355595.1
600304	3264.2088
600305	12690.232
600309	14740.841
600440	4401.0328
600447	3439.0011
607114	26327.474
607234	18964.419
607235	9917.0672
607240	5930.7094
607241	8302.817
607248	5935.7247
607256	2495.307
607257	12208.315
607259	14985.034
607260	71998.184
607262	2670.8554
607263	11184.666
607279	449.27924
607296	6142.8894
607299	10734.967
607300	4669.2042
607305	4078.696
607314	7224.505
607315	9759.8299
607359	6742.7094
608344	307.59145
608499	1496.8174
608501	236.60163
608502	479.15202
608503	2124.6348
608627	7973.3361
609426	13359.804
609427	13079.209
609428	35046.933
609431	42447.42
609434	10372.408
609472	3803.5816
609473	4977.0702
609481	1121.2113
609574	568.51385
609578	9615.9451
609609	14749.54
609661	2369.3326
611387	1985.5187
611388	3389.3431

cheers

Andrew

Re: Stdev calculation for rolling 12 Months

vinod_nallapaneni — Wed, 05 Jul 2017 04:32:36 GMT

Hi Andrew,

Thanks for your response.

Firstly I'm having same format in QV. I think that doesn't require cross table. Is there any possibility to do it in front-end?

Please let me know if it is possible.

-Vinod.

Re: Stdev calculation for rolling 12 Months

Anil_Babu_Samineni — Wed, 05 Jul 2017 08:02:36 GMT

stalwar1‌ mrkachhiaimp‌ marcowedel‌

please help him, Free to ask if required to work related. His intention mainly is Standard Deviation and then Rolling month for that standard deviation.

I even don't know how to write set analysis for Standard deviation. Can you try this for me?

If(Only({< MonthYear ={">=$(=Date(addmonths(Max(MonthYear), -11), 'MMM-YYYY')) <=$(=Date(addmonths(Max(MonthYear), 0), 'MMM-YYYY'))"}>} MonthYear), Stdev(Aggr(Avg(Quantity), MonthYear))

Re: Stdev calculation for rolling 12 Months

MarcoWedel — Wed, 05 Jul 2017 23:02:12 GMT

Hi,

maybe one solution might be:

Stdev({$<MonthYear={"=MonthYear>AddMonths(Max(Total MonthYear),-6) and MonthYear<=Max(Total MonthYear)"}>} Quantity)

tabTemp:
CrossTable (MonthYearTxt, Quantity)
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1300268-285808/Rolling%20Months%20STDEV.xlsx] (ooxml, embedded labels, table is Sheet1);

table1:
LOAD [Product Code],
     MonthName(Evaluate(MonthYearTxt)) as MonthYear,
     Quantity
Resident tabTemp
Where Evaluate(MonthYearTxt) and IsNum(Quantity);

DROP Table tabTemp;

hope this helps

regards

Marco

Re: Stdev calculation for rolling 12 Months

vinod_nallapaneni — Thu, 06 Jul 2017 03:47:41 GMT

Hi Anil,

Thanks for your support.

I've tried your expression but it was not working. As I understood it was calculating first avg and then stdev. So, it is failed to calculate stdev for 1 value.

-Vinod.

Re: Stdev calculation for rolling 12 Months

vinod_nallapaneni — Thu, 06 Jul 2017 03:56:38 GMT

Hi Marco,

You are right. But I want to calculate not only stdev but also other formulas. While I'm using cross table it is working but it was effecting on other formulas. Is there any other solution apart from cross table? I want to calculate rolling months Stdev in front-end. Please let me know if you have any other idea.

-Vinod.

Re: Stdev calculation for rolling 12 Months

MarcoWedel — Fri, 07 Jul 2017 06:08:50 GMT

You might load with and without crosstable in the same application to implement this solution while keeping your existing ones.

Regards

Marco

Re: Stdev calculation for rolling 12 Months

MarcoWedel — Fri, 07 Jul 2017 21:39:52 GMT

Hi,

I guess without crosstable loading your sample table (except for generic field renaming) you would have to deal with monthly changing field names that do not contribute to a combined MonthYear field.

If for whatever reason you cannot change the data model accordingly, one front end solution might be:

(not meant as a recommended solution but as proof that (like in most cases) it could be done in QlikView)

RangeStdev([May 2017],[Apr 2017],[Mar 2017],[Feb 2017],[Jan 2017],[Dec 2016])

or without using static field names:

RangeStdev($(=Concat('['&MonthName(AddMonths(Today(),-ValueLoop(2,7)))&']',',')))

(to be changed to ValueLoop(0,5) if, unlike in this example, data is available up to the current month)

tabTemp:
CrossTable (ColNam, ColHdr)
LOAD 1,* FROM [https://community.qlik.com/servlet/JiveServlet/download/1300268-285808/Rolling%20Months%20STDEV.xlsx] (ooxml, no labels, table is Sheet1)
Where RecNo()=1;

mapFieldNames:
Mapping
LOAD ColNam,
    If(Evaluate(ColHdr),MonthName(Evaluate(ColHdr)),ColHdr)
Resident tabTemp;

DROP Table tabTemp;

table1:
LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/1300268-285808/Rolling%20Months%20STDEV.xlsx] (ooxml, no labels, header is 1 lines, table is Sheet1);

RENAME Fields using mapFieldNames;

hope this helps

regards

Marco