topic Re: Average with Duplicate data in QlikView

Average with Duplicate data

Tue, 30 Sep 2014 18:26:44 GMT

Hello,

I data like this. I want to calculate average for each product. But the issue is for example: Product aaa has 4 values in two different days. I want to Calculate the average for the maximum date 8/17/2014. and average should be 3.25 but qlikview is taking the average of all the 4 values abd giving me 2.87 which is not what I want.

I have this formula but not sure where I am doing wrong:

avg({$<[Date]={"=$(=Date(FirstSortedValue(aggr(max([Date]),Product,[Date]),-aggr([Date],Product,[Date])) ,'M/DD/YYYY'))"}>}Value)

Product	Value	Date
aaa	3	8/17/2014
aaa	3.5	8/17/2014
aaa	2.5	7/12/2013
aaa	2.5	7/13/2013
bbb	3	5/17/2014
ccc	4	6/17/2014
ccc	4	6/18/2014

Any Help?

Thanks in advance.

Re: Average with Duplicate data

maxgro — Tue, 30 Sep 2014 20:08:36 GMT

maybe

avg(aggr(if(max(TOTAL <Product> Date)=Date, avg(Value)), Product, Date))

Re: Average with Duplicate data

Tue, 30 Sep 2014 20:36:55 GMT

Thanks Massimo. For some values this is working but for some it is not working. Here is my sample data:

Product	Value	Date
A	3	5/22/13 7:43 PM
A	3	5/22/13 7:43 PM
F	4	11/20/13 8:57 PM
F	4	11/20/13 8:57 PM
M	3	10/4/12 3:21 PM
M	3	10/4/12 3:21 PM
M	3.5	9/17/13 4:21 PM
M	3.5	9/17/13 4:21 PM
N	3	9/27/12 3:21 PM
N	3.5	9/27/12 3:21 PM
O	3.5	7/8/12 2:09 PM
O	3	7/8/12 2:09 PM
Pe	3.5	3/17/14 1:43 PM
Pe	3	3/17/14 1:43 PM
Ps	2.5	9/28/12 11:04 AM
Ps	2.5	9/28/12 11:04 AM
Ps	3	8/11/13 4:26 PM
Ps	3	8/11/13 4:26 PM
Su	3.5	1/24/14 4:56 PM
Su	3.5	1/24/14 4:56 PM

my desired output is:

Product	AvgValue	Date
O	3.25	7/8/12 2:09 PM
N	3.25	9/27/12 3:21 PM
A	3	5/22/13 7:43 PM
Ps	3	8/11/13 4:26 PM
M	3.5	9/17/13 4:21 PM
F	4	11/20/13 8:57 PM
Su	3.5	1/24/14 4:56 PM
Pe	3.25	3/17/14 1:43 PM

Thanks

Re: Average with Duplicate data

maxgro — Tue, 30 Sep 2014 20:46:45 GMT

and where are the wrong values?

Re: Re: Average with Duplicate data

maxgro — Tue, 30 Sep 2014 20:48:28 GMT

I used this script

a:
LOAD
Product, Value,
timestamp(Date#(Date, 'MM/DD/YYYY hh:mm TT')) as Date
FROM [http://community.qlik.com/thread/135862] (html, codepage is 1252, embedded labels, table is @2);

Re: Average with Duplicate data

Tue, 30 Sep 2014 21:00:58 GMT

Thanks. Sorry it is working. I need to add another condition to the formula. ID =2. Can I add to it to this formula?