Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Creator

Aggregated average based on firstsortedvalue - Help

Hello everyone,

Please help me I am struggling with calculation of aggregated average.

I have data like this representing the mileage at different point of time for different vehicles.

Vehicle Date-Time  Mileage

See data enclosed.

I would like to have one calculation representing the average mileage for our population based on first and last date of the month for each vehicle.

For example : 5th June vehicle A has 10.000 km 25th June it has 15.000 km it drove 5000 km in 20 days so avg mileage per month is 30*5000/20 = 7500 km/month  (if the vehicle has data between these 2 dates we ignore them, we just want to know the first and last date of each month per vehicle)

8th of June vehicle B has 8.000 km and 15th of June it has 10.000km so average is 30*2000/7=8571 km/month

So for both vehicle average mileage in June is 8035 (7500+8571/2)

This 8035 I would like to calculate it in a single formula.

Could you please help me  and apply the same logic to my data ?

In advance thanks for your help

1 Solution

Accepted Solutions
MVP

Try this

Avg(Aggr((FirstSortedValue(VALUE, -READOUT_DATETIME) - FirstSortedValue(VALUE, READOUT_DATETIME)) * 30/(Max(DATE) - Min(DATE)), Vehicle, MonthYear))

11 Replies
MVP

What is the expected output based on the sample you have provided?

Creator
Author

Basically what I need to have is the last 2 results/tables on the right.

Here I represented the different calculation step to retrieve average per vehicle and per month.

Thank you

MVP

From 555508 and 566864 for Vehicle A in November, how are you getting 12167 in the second table?

Creator
Author

30*(566864-555508)/(28)

28 represents the days difference between 1/11 and 29/11

MVP

Try this

Avg(Aggr((FirstSortedValue(VALUE, -READOUT_DATETIME) - FirstSortedValue(VALUE, READOUT_DATETIME)) * 30/(Max(DATE) - Min(DATE)), Vehicle, MonthYear))

Creator
Author

Thank you I adapted the formula a little bit and it is working perfectly

MVP

Awesome

Creator
Author

Another question linked to this exercise

What if I want to display the latest value of this aggregation in a table

Of course last month can be different for each vehicle

Example

Vehicle   Last month mileage

A               7135

B               8086

Thank you

Creator
Author

The idea is to have the avg monthly mileage per vehicle but also the latest month mileage to see if there is any deviation

Community Browser