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

Calculate limited average for field's values

vlad_komarov — Wed, 22 Dec 2021 15:42:54 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 06:59:22 GMT

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

Anil_Babu_Samineni — Sat, 20 Aug 2016 10:58:38 GMT

Expecting O/P from above Data?

Re: Calculate limited average for field's values

vlad_komarov — Mon, 22 Aug 2016 03:16:34 GMT

Anil,

Not sure I've got your question..