topic Re: Pick First Record in QlikView

Pick First Record

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Pick First Record

adamdavi3s — Tue, 21 Mar 2017 11:04:55 GMT

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))

Re: Pick First Record

sunny_talwar — Tue, 21 Mar 2017 11:06:54 GMT

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))

Re: Pick First Record

Tue, 21 Mar 2017 11:21:48 GMT

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.

Re: Pick First Record

sunny_talwar — Tue, 21 Mar 2017 11:24:30 GMT

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