3 Replies Latest reply: Aug 21, 2016 11:16 PM by Vladimir Komarov

# 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

I think rather not. You will quite probably need a group by to get the sum per day and those result then matching with one or two peek-loadings (with different sortings to get the previous records from both sites) and afterwards a further aggregation to get the average.

Alternative to the mentioned peek-runs could I think on a kind of As-of-Table within the script (each day is there matched with the 10 days prior and after it) and which will be taken to flag the right days together. A different way might be to match the days simply to their RowNo in 20-buckets (but it will be difficult if there gaps between them).

In conclusion I think there would be alternatives possible but I'm not sure if they would be more elegant or better perform than the "classical" way.

- Marcus

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

Expecting O/P from above Data?

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

Anil,

Not sure I've got your question..

VK