Value on Max and Min Date Not Working

[#1]

unit	key	date
150	A	5/17/2009
225	A	6/29/2009
300	A	10/19/2009
100	B	2/16/2008
200	B	4/16/2008
300	B	7/13/2008

Dear all,

I have the above set of data and what would like to achieve is to have the value of unit in the max and min date for each of the key, which is shown as below.

key	unit at max date	unit at min date
A	300	150
B	300	100

But what I get is

key	unit at max date	unit at min date
A	0	150
B	0	100

I tried to use set analysis but I can't figure out the way to do it. The expressions I used are

sum(if(date=aggr(max(total<key> date),key),unit))

sum(if(date=aggr(min(total<key> date),key),unit,0))

Attached is the qvw file. Would you please help?

[MK_QSL]

Use below script

========================================================

Data:

Load

  unit,

  key,

  Date(Date#(date,'M/DD/YYYY')) as date

Inline

[

  unit, key, date

  150, A, 5/17/2009

  225, A, 6/29/2009

  300, A, 10/19/2009

  100, B, 2/16/2008

  200, B, 4/16/2008

  300, B, 7/13/2008

];

Join

Load key, Date(min(date)) as MinDate Resident Data Group By key;

Join

Load key, Date(max(date)) as MaxDate Resident Data Group By key;

========================================================

Now create a Straight Table

Dimension

key

Expression

SUM(IF(date = MinDate, unit))

SUM(IF(date = MaxDate, unit))

========================================================

I strongly believe that the solution provided by tresesco is the best one

[jyothish8807]

Thanx tresco. It is new for me.Can please tell me how it works? How is it sorting?.

Regards

KC

[tresesco]

Set analysis evaluates once for an object and not row-wise, hence for both the keys it gets max date as -10/19/2009 (which is global), hence the result. Hope this helps you comprehend.