Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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)


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

5 Replies
Specialist II
Specialist II

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


Modify as required in your scenario




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)


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

talk is cheap, supply exceeds demand
Not applicable


with the data loaded inline, I made it work.

I attached the qvw, hope it helps.


Not applicable

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:


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.