Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Thank you for this reactive support
Much appreciated