Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I believe
=Sum(Aggr([End Date] - [Start Date], ID)) / Count(Distinct ID)
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
I believe
=Sum(Aggr([End Date] - [Start Date], ID)) / Count(Distinct ID)
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Hi - Rob's Solution works perfectly fine.
if you want to calculate the avg price in script then May be this?
Thanks Rob - That works