Calculate limited average for field's values

[vlad_komarov]

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

[marcus_sommer]

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

[Anil_Babu_Samineni]

Expecting O/P from above Data?

[vlad_komarov]

Anil,

Not sure I've got your question..

VK