7 Replies Latest reply: Aug 24, 2016 1:34 PM by Dyhessyca Correa

# Calculate limited average for field's values

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...

Regards,

• ###### Re: Calculate limited average for field's values

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

VK

• ###### Re: Calculate limited average for field's values

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

Data:

[Traffic Enters],

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

FROM

Data.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join(Data)

//_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)

_p_date -10 as _p_date,

Resident Data;

Left Join(Data)

_p_date,

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/20162421242124192
02/01/20162020444126543
03/01/20162206664728272
04/01/20162526917330130
05/01/201621581133132440
06/01/201621091344035040
07/01/201622091564937180
08/01/201624851813439051
09/01/201617061984041528
10/01/201622062204643962
11/01/201621462419245941
12/01/20162351265432421481132175.8095238095
13/01/20161729282724441505132193.9047619048
14/01/20161858301306647529112203.0476190476
15/01/20162310324409173550012182.2857142857
16/01/201626003504011331570672177.9047619048
17/01/201621403718013440591452176.4285714286
18/01/201618713905115649614392180.4761904762
19/01/201624774152818134633202151.7142857143
20/01/201624344396219840655342175.9047619048
21/01/201619794594122046676072169.5714285714
22/01/201621724811324192700242182.4761904762
23/01/201624005051326543717962154.9047619048
24/01/201623985291128272741052182.5238095238
25/01/201620905500130130766812216.7142857143
26/01/201620665706732440790372218.9047619048
27/01/201620785914535040808522181.5238095238
28/01/201622946143937180831092187.0952380952
29/01/201618816332039051855312213.3333333333
30/01/201622146553441528878872207.5714285714
31/01/201620736760743962904232212.4285714286

Andrew

• ###### Re: Calculate limited average for field's values

Andrew,

Thanks!

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

VK

• ###### Re: Calculate limited average for field's values

thank you

• ###### Re: Calculate limited average for field's values

Dyhessyca,

Not sure if you are asking or suggesting...

I have some issues implementing the logic suggested by Andrew Walker 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.

VK

• ###### Re: Calculate limited average for field's values

I was suggesting...

Ok, thank you.