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]

Hey Chris,

Its simple if you do the manipulations on the back-end.

Check out the attached file and see if it helps.

Thanks

AJ

[#3]

Thanks.  I need execute the expression on the front end though, as I would like the count / balance info to update base don user's selection.

Do you have any insight on how I could accomplish this on the front end?

Thanks,

Chris

[MK_QSL]

is it what you are looking for?

[#5]

Not quite.  for Account 123, only the most recent records (from 2/1//2012) should be displayed.  With no selections the expression should ignore records from 1/1/2012 for account 123, and pick them up for Account 124.

Thanks,

Chris

[#6]

What I did was just join the two tables as per your logic. It will change per users selection. The calculations are still made on the front end. All thats being done is just group by and join which shouldn't affect the results.

Can you give an example if you think its not working when user makes a selection?

[#7]

I added a multi box containing effective date.  When i select 40909 (1/1/2012) I would like to see Account 124 with a balance of 25 and count of 2, which your solution does.  But based on my selction of 1/2/2012 I would like to see Account 123 with a count of 2 and balance of 8.

Essentially based on the users selection the balances associated with the most recent effect date should be displayed.

[#8]

How do you get 8 when you select 2/1/2012. Can you please elaborate?

[#9]

hi chris Please find Attachment i some what improved doc of Manish

hpoe this helps

[#10 · Accepted solution]

I figured out the solution I was looking for.

here is what I used for the Balance

Firstsortedvalue(AGGR(sum({<Status={'Open'}>}Balance),EffectiveDate,Account),-AGGR((EffectiveDate), Account, EffectiveDate)

and count:

Firstsortedvalue(AGGR(Count({<Status={'Open'}>}Balance),EffectiveDate,Account),-AGGR((EffectiveDate), Account, EffectiveDate)

Thanks very much for your help and suggestions!

Updated 3/5/14 per Manish's comments.  I mistakenly omitted that Status should be Open.