First Sorted Value, set analysis and aggr.. it's aggravating!

[#1]

Simple enough task: Find the last call where the ActCode was "MDC" and the person who made it was the CSR assigned to the account. Then, find the last  and second last call by the CSR that DOES NOT have 'MDC' as the ActCode. Here's a small sample of the data:(what I want for the first call is 5/6/15 10:05:59, and the second call is 5/5/15 9:10:34)

First attempt was this: =FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime)

which works, except that it returns "5/6/15 16:26:34" , where the HistUser (the user who made the call) was not the CSR.

So, I tried =FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={GMA.HistUser}>} GMA.FullTime, -GMA.FullTime)

but that returns null. 

So I tried =if(GMA.CSR=GMA.HistUser,FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime))

but that returns null.

I tried =aggr(FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime),GMA.CSR)

which returns 5/6/15 16:26:34 (and which is correct, since this is the last non-MDC call aggregated by CSR, but it doesn't give me what I want).

How do I specify: the last (and the second last) call made where the ActCode is NOT "MDC" and the HistUser=CSR?

thanks, Kevin

[Anonymous]

You can try with Concat cat function with substring for 1st and second record

=SubField(Concat([Col],'@',1/[GroupCol]),'@',1)

Modify as required in your scenario

Thanks

BKC

[sunny_talwar]

This expression is incorrect:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={GMA.HistUser}>} GMA.FullTime, -GMA.FullTime)

Should be like this:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"=[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime, -GMA.FullTime)

[Gysbert_Wassenaar]

Should be like this:

=FirstSortedValue({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime, -GMA.FullTime)

That's the same as
=Max({$<[GMA.ActCode]-={'MDC'},[GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"}>} GMA.FullTime)

And [GMA.CSR]={"[GMA.CSR] = [GMA.HistUser]"} may or may not give correct results. Judging from the screenshot I'd say it won't. See set_analysis_intra-record.qvw

[#5]

Hi,

with the data loaded inline, I made it work.

I attached the qvw, hope it helps.

Regards

[#6]

Thanks to everyone for their replies. In the end, I went back to the user, and found out they are not interested in the actions of anyone besides the assigned CSR (GMA.CSR), so I added to my script:

WHERE GMA.CSR=GMA.HISTUSER

and then was able to use

=aggr(FirstSortedValue({$<[GMA.ActCode] -= {'MDC'} > } GMA.FullTime,-GMA.FullTime),GMA.COMPANY)

Again, I thank everyone for their help, and especially Gysbert - his link edified some issues that had mystified me.

cheers,

Kevin