topic Re: Calculate limited average for field's values in QlikView

Calculate limited average for field's values

vlad_komarov — Fri, 19 Aug 2016 22:17:21 GMT

Hello,

I am looking for non-trivial solution for my task:

I do have data table that contains some values (people's daily counts) for multiple locations over time (for ~3 years)

I need to calculate (for each day) the Average Traffic 10 days prior and 10 days after the particular date.

It's easy to do in the chart:

rangeavg(

below(sum([Traffic Enters]), 0, 10),

above(sum([Traffic Enters]), 1, 10)

)

But I need to do it in the script...

Yes, there is a way using GROUP BY, FOR TO - NEXT, PEEK, etc approach...

But I was hoping somebody could suggest a better and more elegant solution...

Looking forward to your suggestions!

Regards,

Vlad

Re: Calculate limited average for field's values

marcus_sommer — Sat, 20 Aug 2016 07:19:47 GMT

Please do not double-posting: https://community.qlik.com/thread/229790

Re: Calculate limited average for field's values

effinty2112 — Sat, 20 Aug 2016 12:33:12 GMT

Hi Vladimir,

Since we're looking at inter-record values I think I should be allowed one PEEK!

Data:

LOAD _p_date,

[Traffic Enters],

RangeSum([Traffic Enters],Peek('Cumulative')) as Cumulative

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

LOAD

//_p_date +10 as _p_date, EDIT : Correction made to earlier post.

//Cumulative as TenDaysAgo

_p_date +11 as _p_date,

Cumulative as ElevenDaysAgo

Resident Data;

Left Join(Data)

LOAD

_p_date -10 as _p_date,

Cumulative as TenDaysAhead

Resident Data;

Left Join(Data)

Load

_p_date,

//(TenDaysAhead - TenDaysAgo)/21 as TwentyOneDayAverage

(TenDaysAhead - ElevenDaysAgo)/21 as TwentyOneDayAverage

Resident Data;

I tried it with some test data since the 1st Jan and got this (showing Jan only)

_p_date	Traffic Enters	Cumulative	ElevenDaysAgo	TenDaysAhead	TwentyOneDayAverage
01/01/2016	2421	2421		24192
02/01/2016	2020	4441		26543
03/01/2016	2206	6647		28272
04/01/2016	2526	9173		30130
05/01/2016	2158	11331		32440
06/01/2016	2109	13440		35040
07/01/2016	2209	15649		37180
08/01/2016	2485	18134		39051
09/01/2016	1706	19840		41528
10/01/2016	2206	22046		43962
11/01/2016	2146	24192		45941
12/01/2016	2351	26543	2421	48113	2175.8095238095
13/01/2016	1729	28272	4441	50513	2193.9047619048
14/01/2016	1858	30130	6647	52911	2203.0476190476
15/01/2016	2310	32440	9173	55001	2182.2857142857
16/01/2016	2600	35040	11331	57067	2177.9047619048
17/01/2016	2140	37180	13440	59145	2176.4285714286
18/01/2016	1871	39051	15649	61439	2180.4761904762
19/01/2016	2477	41528	18134	63320	2151.7142857143
20/01/2016	2434	43962	19840	65534	2175.9047619048
21/01/2016	1979	45941	22046	67607	2169.5714285714
22/01/2016	2172	48113	24192	70024	2182.4761904762
23/01/2016	2400	50513	26543	71796	2154.9047619048
24/01/2016	2398	52911	28272	74105	2182.5238095238
25/01/2016	2090	55001	30130	76681	2216.7142857143
26/01/2016	2066	57067	32440	79037	2218.9047619048
27/01/2016	2078	59145	35040	80852	2181.5238095238
28/01/2016	2294	61439	37180	83109	2187.0952380952
29/01/2016	1881	63320	39051	85531	2213.3333333333
30/01/2016	2214	65534	41528	87887	2207.5714285714
31/01/2016	2073	67607	43962	90423	2212.4285714286

Andrew

Re: Calculate limited average for field's values

vlad_komarov — Mon, 22 Aug 2016 03:09:12 GMT

Sorry, Marcus... QS forum is much smaller, so I've decided to post the same question in QV forum as well...

Re: Calculate limited average for field's values

vlad_komarov — Mon, 22 Aug 2016 03:11:43 GMT

Andrew,

Thanks!

I like the idea, will check it first thing on Monday.

Re: Calculate limited average for field's values

Anonymous — Wed, 24 Aug 2016 17:28:07 GMT

Please, check the correct answer.

thank you

Re: Calculate limited average for field's values

vlad_komarov — Wed, 24 Aug 2016 17:33:20 GMT

Dyhessyca,

Not sure if you are asking or suggesting...

I have some issues implementing the logic suggested by effinty2112‌ above...

My real case is a bit complicated with multiple dimensions and I am fixing the joins for this case.

Will post my comments as soon as I will fix my code.

Re: Calculate limited average for field's values

Anonymous — Wed, 24 Aug 2016 17:34:45 GMT

I was suggesting...

Ok, thank you.