Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average with Duplicate data

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)

ProductValueDate
aaa38/17/2014
aaa3.58/17/2014
aaa2.57/12/2013
aaa2.57/13/2013
bbb35/17/2014
ccc46/17/2014
ccc46/18/2014

Any Help?

Thanks in advance.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

maybe

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

1.png

View solution in original post

5 Replies
maxgro
MVP
MVP

maybe

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

1.png

Not applicable
Author

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

ProductValueDate
A35/22/13 7:43 PM
A35/22/13 7:43 PM
F411/20/13 8:57 PM
F411/20/13 8:57 PM
M310/4/12 3:21 PM
M310/4/12 3:21 PM
M3.59/17/13 4:21 PM
M3.59/17/13 4:21 PM
N39/27/12 3:21 PM
N3.59/27/12 3:21 PM
O3.57/8/12 2:09 PM
O37/8/12 2:09 PM
Pe3.53/17/14 1:43 PM
Pe33/17/14 1:43 PM
Ps2.59/28/12 11:04 AM
Ps2.59/28/12 11:04 AM
Ps38/11/13 4:26 PM
Ps38/11/13 4:26 PM
Su3.51/24/14 4:56 PM
Su3.51/24/14 4:56 PM

my desired output is:

ProductAvgValueDate
O3.257/8/12 2:09 PM
N3.259/27/12 3:21 PM
A35/22/13 7:43 PM
Ps38/11/13 4:26 PM
M3.59/17/13 4:21 PM
F411/20/13 8:57 PM
Su3.51/24/14 4:56 PM
Pe3.253/17/14 1:43 PM

Thanks

maxgro
MVP
MVP

1.png

and where are the wrong values?

maxgro
MVP
MVP

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

Not applicable
Author

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