Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
maybe
avg(aggr(if(max(TOTAL <Product> Date)=Date, avg(Value)), Product, Date))
maybe
avg(aggr(if(max(TOTAL <Product> Date)=Date, avg(Value)), Product, Date))
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
and where are the wrong values?
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);
Thanks. Sorry it is working. I need to add another condition to the formula. ID =2. Can I add to it to this formula?