Average of last 4 by time

c_latham — Sat, 16 Nov 2024 03:15:27 GMT

Hi All,

I am attempting to work out a rolling average based on the last 4 visits a car had calculated by a visit time. Data as shown below:

VisitID	CarName	VisitDate	DurationMins
1281	Mercedes	16/02/2020 13:00:00.000	37
9827	BMW	16/02/2020 12:47:29.000	23
9848	BMW	13/02/2020 09:32:34.000	24
3923	Mercedes	12/02/2020 06:14:32.000	65
2819	Mercedes	11/02/2020 08:32:50.000	12
1209	Ford	10/02/2020 02:02:08.000	68
1987	Mercedes	13/02/2020 18:00:07.000	24
2403	Mercedes	11/02/2020 19:32:03.000	16

I would want an extra column on the table with an average like the below:

VisitID	CarName	VisitDate	DurationMins	4 Average
1281	Mercedes	16/02/2020 13:00:00.000	37	34.5
9827	BMW	16/02/2020 12:47:29.000	23
9848	BMW	13/02/2020 09:32:34.000	24
3923	Mercedes	12/02/2020 06:14:32.000	65
2819	Mercedes	11/02/2020 08:32:50.000	12
1209	Ford	10/02/2020 02:02:08.000	68
1987	Mercedes	13/02/2020 18:00:07.000	24
2403	Mercedes	11/02/2020 19:32:03.000	16

I am attempting to use something like:
=avg(aggr(FirstsortedValue(DurationMins,VisitDate),CarName))

But I cannot get it to fit my requirement of last 4 visits.

Any help would be great!

Re: Average of last 4 by time

TimvB — Sun, 16 Feb 2020 20:18:19 GMT

I do not understand the final measure that you need and why you use FirstSortedValue(), so I cannot provide a working expression. However, if you would like to calculate an average over the last four rows the function Below() will do the job.

Hope it helps!

Re: Average of last 4 by time

c_latham — Mon, 17 Feb 2020 11:06:15 GMT

I was just attempting to get something to work...

Thanks to your pointer I then have gone for:

=Aggr(RangeAvg(Below(([DurationMins]),0,4)), 0, [CarName], VisitDate)

This gives me no results 😣

topic Re: Average of last 4 by time in App Development

Average of last 4 by time

Re: Average of last 4 by time

Re: Average of last 4 by time