Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted

Try this

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


Capture.PNG

View solution in original post

11 Replies
Highlighted

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

Highlighted
Contributor III
Contributor III

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

Highlighted

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

Highlighted
Contributor III
Contributor III

30*(566864-555508)/(28)

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

Highlighted

Try this

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


Capture.PNG

View solution in original post

Highlighted
Contributor III
Contributor III

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

Highlighted

Awesome

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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