Aggregation and average for latest values on multiple identifiers

[#1]

Hello All,

I have another aggregation and average problem if someone can help me. I have a table something like this

Customer ID     Vehicle               Mileage               Date

1                         A                         5000               01/01/2016

1                         A                       10000                02/01/2016                   

2                         B                       10000                01/15/2016

2                         B                       20000                02/15/2016

2                         C                       20000               02/10/2016

2                         C                       30000               03/26/2016

I have to find the average of latest mileage for vehicles for each customer. So for customer A there is only one vehicle A, so I find the latest value which is 10000.

For customer 2, there are 2 vehicles so I need to find out the latest mileage for both the vehicles and average them so latest mileage for vehicle B is 20000 and latest mileage for vehicle C is 30000 so I will get the average for 20000 and 30000.

My result table will be

Customer            AvgMileage

1                         10000

2                         25000

Is there a way to do this?

thanks,

Manoj

[sunny_talwar · Accepted solution]

Try this:

Dimension

Customer ID

Expression:

=Avg(Aggr(FirstSortedValue(Mileage, -Date), [Customer ID], Vehicle))

[#3]

Thanks Sunny,

I was missing the customer id in the aggregate function and then I solved it by summing and then divide by number of vehicles but your solution is simpler and better.

regards,

[#4]

Sunny,

I have seen an issue with function FirstSortedValue. If there are 2 transactions on the same date then I get a null so if i have something like

Customer ID     Vehicle               Mileage               Date

1                         A                       10000               01/01/2016

1                         A                       10000               01/01/2016      

I will get a null in the average mileage. Any idea how to work in such cases?

thanks,

[sunny_talwar]

What would be the required output in this case?

May be try with adding DISTINCT in there:

=Avg(Aggr(FirstSortedValue(DISTINCT Mileage, -Date), [Customer ID], Vehicle))