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?

[tresesco]

Try:

FirstSortedValue(unit, date)  //min

FirstSortedValue(unit, -date)  //max

[rbecher]

Hi Ivy,

you can use set analysis:

sum({<date={'$(=min(date))'}>} unit)

sum({<date={'$(=max(date))'}>} unit)

- Ralf

[#4]

It works! Thanks a lot. I wonder why mine working working? Also, could it be done using set analysis?

[jyothish8807]

PFA

Regards

KC

[jyothish8807]

Hi Tresco,

Will it work if the date order is suffeled in the source sheet?

Regards

KC

[tresesco]

Yes, it will.

[#8]

Thanks Ralf. It seems that using set analysis is not working properly

[#9]

It is what I get using set analysis

[jyothish8807]

Try using my attachment.Logic is little different in that.

Regards

KC