Pick First Record

[#1]

Hi All,

I have an expression to calculate number of clients who opened a new account. The problem rises where a client has opened multiple accounts on different dates, which in this case I need to show them against first opening date.

I am using below expression which seems picks the latest opening date for a client. Any idea on how I can get the first opening date would be appreciated.

Sum(  aggr(  if(

sum(aggr(

FirstSortedValue( distinct {<EFFECTIVE_DATE={"$(=vEndDate)"}>} Amount,-EFFECTIVE_DATE),Account_ID))<=0 and min(EarliestOpen)>=$(vStartDate) and min(EarliestOpen)<=$(vEndDate),1,0),Customer_ID))

Thanks

Neda

[adamdavi3s]

Hi Neda,

Remove the - against EFFECTIVE_DATE

Sum(  aggr(  if(

sum(aggr(

FirstSortedValue( distinct {<EFFECTIVE_DATE={"$(=vEndDate)"}>} Amount,EFFECTIVE_DATE),Account_ID))<=0 and min(EarliestOpen)>=$(vStartDate) and min(EarliestOpen)<=$(vEndDate),1,0),Customer_ID))

[sunny_talwar]

May be remove the negative sign next to the EFFECTIVE_DATE

Sum(Aggr(If(

Sum(Aggr(FirstSortedValue(DISTINCT {<EFFECTIVE_DATE={"$(=vEndDate)"}>} Amount, -EFFECTIVE_DATE), Account_ID)) <= 0 and Min(EarliestOpen)>=$(vStartDate) and Min(EarliestOpen)<=$(vEndDate), 1, 0), Customer_ID))

Try this:

Sum(Aggr(If(

Sum(Aggr(FirstSortedValue(DISTINCT {<EFFECTIVE_DATE={"$(=vEndDate)"}>} Amount, EFFECTIVE_DATE), Account_ID)) <= 0 and Min(EarliestOpen) >= $(vStartDate) and Min(EarliestOpen) <= $(vEndDate), 1, 0), Customer_ID))

[#4]

Thanks guys, I think I wasn't clear on the requirement. firstsortedvalue is returning as expected but when I chart client against opening date(EarliestOpen), it would show client vs. last opening date. somehow the expression points to the last record for a client with mutiple accounts.

[sunny_talwar]

Would you be able to share a sample to look at this?