Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

View solution in original post

Not applicable

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

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