Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
c_latham
Contributor III
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 
 1281Mercedes 16/02/2020 13:00:00.000 37 
 9827BMW 16/02/2020 12:47:29.000 23 
 9848BMW 13/02/2020 09:32:34.000 24 
 3923Mercedes 12/02/2020 06:14:32.000 65
 2819Mercedes11/02/2020 08:32:50.000 12 
 1209Ford 10/02/2020 02:02:08.000 68 
 1987Mercedes 13/02/2020 18:00:07.000 24 
 2403Mercedes 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
 1281Mercedes 16/02/2020 13:00:00.000 37 34.5
 9827BMW 16/02/2020 12:47:29.000 23  
 9848BMW 13/02/2020 09:32:34.000 24  
 3923Mercedes 12/02/2020 06:14:32.000 65 
 2819Mercedes11/02/2020 08:32:50.000 12  
 1209Ford 10/02/2020 02:02:08.000 68  
 1987Mercedes 13/02/2020 18:00:07.000 24  
 2403Mercedes 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)
2 Replies
TimvB
Creator II
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!

c_latham
Contributor III
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)

This gives me no results 😣