# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Contributor III

## Average of last 4 by time

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!

Labels (3)

• ### Qlik Sense

2 Replies
Creator II

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!

Contributor III
Author

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)