5 Replies Latest reply: May 8, 2015 1:47 PM by Kevin Bertsch RSS

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

      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)

      mdc.PNG

      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