How to calculate average age per id instead of per line

[JustinM]

Hi

I'm trying to work out average age of a number of ids with multiple rows of same id. Currently I'm only getting it to calulate the average based on the number of rows instead of the ids.

I have the following Data:

ID	Start Date	End Date	Age
1	01-Jan-22	20-Jan-22	19
1	01-Jan-22	20-Jan-22	19
1	01-Jan-22	20-Jan-22	19
2	10-Jan-22	15-Jan-22	5
2	10-Jan-22	15-Jan-22	5
3	11-Jan-22	25-Jan-22	14

I am looking for an answer of 13 (19+5+14)/3  but I'm getting 14 (81/6)

My current formula is (sum([End Date])-sum([Start Date]))/count([ID])

i'm thinking I need to build some kind of distinct count or similar in

Any help is appreciated

[rwunderlich · Accepted solution]

I believe 

=Sum(Aggr([End Date] - [Start Date], ID)) / Count(Distinct ID) 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Qrishna]

Hi - Rob's Solution works perfectly fine.

if you want to calculate the avg price in script then May be this?

[JustinM]

Thanks Rob - That works