FirstSortedValue Help

[#1]

Hi,

I'm having some trouble using FirstSortedValue and could use some help.

I have the following Tables: AccountInfo and BalanceInfo

AccountInfo looks like:  

Key	Account#	EffectiveDate
12301012012	123	1/1/2012
12401012012	124	1/1/2012
12302012012	123	2/1/2012

Balanceinfo Looks like:

Key	BaLID	Balance	Status
12301012012	1	3.00	Open
12301012012	2	5.00	Open
12401012012	3	15.00	Open
12401012012	4	10.00	Open
12302012012	5	15.00	Open
12302012012	6	20.00	Closed
12302012012	7	30.00	Open

I have a 1:M relationship between Account and Balance Info.  I'd like to be able to return the count and balances associated with the most recent open accounts.  So for Account 123 I would return count of 2 and balance of 45.  For account 124 I would want to return count of 2 and balance of 25.

I'm currently using sum((AGGR(firstsortedvalue({<Status={'Open'}>}Balance,-EffectiveDate),Account))) to calculate the total Balance, which seems to work, but I can't figure out how to determine the count.

I assumed using firstsortedvalue would be the way to go, but I am open to other suggestions.

Any help would be appreciated.

[#2]

hi chris,

if u found Solution  then please mark this Discussion as answered

so it will help those who are facing same type of problem

thanks And regards

[MK_QSL]

We are glad that you found your solution.

Just for curiosity, what was wrong in our solution?

After checking your solution, found that it will not work for Status Open as your have given generalized answer and nowhere mentioned Status = OPEN !!!

Can you check our solutions also and match with your one so that you can identify what was wrong /