topic Re: Aggregation and average for latest values on multiple identifiers in QlikView

Aggregation and average for latest values on multiple identifiers

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregation and average for latest values on multiple identifiers

sunny_talwar — Thu, 19 May 2016 20:42:19 GMT

Try this:

Dimension

Customer ID

Expression:

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

Re: Aggregation and average for latest values on multiple identifiers

Thu, 19 May 2016 20:56:54 GMT

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,

Re: Aggregation and average for latest values on multiple identifiers

Fri, 20 May 2016 18:52:07 GMT

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

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

thanks,

Re: Aggregation and average for latest values on multiple identifiers

sunny_talwar — Fri, 20 May 2016 18:56:46 GMT

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))