topic Re: Aggregated average based on firstsortedvalue - Help in QlikView

Aggregated average based on firstsortedvalue - Help

Hakim-A — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

sunny_talwar — Thu, 15 Feb 2018 13:09:44 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Thu, 15 Feb 2018 13:26:58 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

sunny_talwar — Thu, 15 Feb 2018 14:23:08 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Thu, 15 Feb 2018 16:21:11 GMT

30*(566864-555508)/(28)

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

Re: Aggregated average based on firstsortedvalue - Help

sunny_talwar — Thu, 15 Feb 2018 16:29:49 GMT

Try this

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

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Thu, 15 Feb 2018 18:41:55 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

sunny_talwar — Thu, 15 Feb 2018 18:54:00 GMT

Awesome

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Thu, 15 Feb 2018 19:12:51 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Thu, 15 Feb 2018 19:14:54 GMT

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

Re: Aggregated average based on firstsortedvalue - Help

sunny_talwar — Thu, 15 Feb 2018 19:20:05 GMT

May be this

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

Re: Aggregated average based on firstsortedvalue - Help

Hakim-A — Fri, 16 Feb 2018 15:38:59 GMT

Thank you for this reactive support

Much appreciated