topic Re: FirstSortedValue and AGGR in QlikView

FirstSortedValue and AGGR

Fri, 11 Dec 2015 14:21:24 GMT

Hi, I am trying to find the last time our reps made contact with the customer and what they entered in the "REF" field. The relevant fields in my table are Sales_Rep, Short_Reference, and RefDate (MM/DD/YY hh:mm). When I put in the RESULT CODE as a dimension, I get the following:

However, I only want to the see the very last entry, not last by ResultCode. But when I take out the ResultCode dimension, I get:

The two RefDates are clearly not the same, so I can't see why FSV can't differentiate between them (which I assume is the reason for the "Null" return). I tried:

=If(HistTab.Result_Code <> Null(), aggr(FirstSortedValue(HistTab.Short_Reference,-HistTab.RefDate),HistTab.Company))

and

=If(HistTab.Result_Code <> Null(),FirstSortedValue( aggr(HistTab.Short_Reference,HistTab.Company),-HistTab.RefDate))

all with the same "null" result.

I'm sure I'm missing something obvious; can anyone help me out? thanks, Kevin

Re: FirstSortedValue and AGGR

Gabriel — Fri, 11 Dec 2015 14:34:33 GMT

Hi,

May be try to include Max into your expression as below

=If(HistTab.Result_Code <> Null(),

FirstSortedValue(

Aggr(

Max(HistTab.Short_Reference,HistTab.Company)),-HistTab.RefDate))

Re: FirstSortedValue and AGGR

sunny_talwar — Fri, 11 Dec 2015 14:36:35 GMT

Have you tried with DISTINCT may be

=If(HistTab.Result_Code <> Null(), FirstSortedValue(DISTINCT HistTab.Short_Reference, -HistTab.RefDate))

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 14:39:24 GMT

Hi Kevin, try with this:

1. Use the same steps that you used in the chart number one.

2. Hide the field: HistTab.Company

3. Order the table by Last Ref Date

4. Show just one value

You cannot take out the dimension Result_Code because this field use the function firstsortedvalue to order the values.

I hope that it help you

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 14:41:41 GMT

Hi Gabriel,

Short_Reference is a string. Max(Short_Reference) won't work, and MaxString would give me, for example, the Ref that starts with "ZZ", which is not what I want. I want the REF with the latest date attached.

thanks, Kevin

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 14:44:25 GMT

But the field Short_Reference (SR) is already unique, as you can see from the first screen shot. So I don't see how adding DISTINCT will help. (And I tried it, and result was still Null).

thanks, Kevin

Re: FirstSortedValue and AGGR

sunny_talwar — Fri, 11 Dec 2015 14:46:48 GMT

Would you be able to share a sample?

Re: FirstSortedValue and AGGR

Gabriel — Fri, 11 Dec 2015 14:55:55 GMT

Hi,

Why not take advantage of the Sort Weigh of FirstSortedValue to rank what to return

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

Re: FirstSortedValue and AGGR

sivarajs — Fri, 11 Dec 2015 15:03:31 GMT

Try

aggr(Maxstring(HistTab.RefDate),HistTab.Company)

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 15:05:03 GMT

Er, that is EXACTLY what the "-HistTab.RefDate" is supposed to do. It weights the sort by date, and the "-" sign indicates to return the last value (as opposed to first) by that criterion.

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 15:09:07 GMT

I must not be expressing the problem very well.

There are TWO values for reference. I don't want the one that is alphabetically first or last (as MaxString would give me). I want the one with the LATEST REFERENCE DATE, which is clearly shown in the table above. The REF dates are unique, the REF strings are unique. In the screenshot above, I ONLY want to see the REF string for the most recent REF date (5/28/14). I thought FirstSortedValue(expr, sortweight) would do exactly that, and in fact it does in most applications. I can't understand why it's not working here.

Re: FirstSortedValue and AGGR

Gabriel — Fri, 11 Dec 2015 15:17:14 GMT

Hi,

Ok that's fine. Thinking of it the sort weight is supposed to return Numeric value. So in that case why not rank the HistTab.RefDate in the script and just return the rank value in your FirstSortedValue (Sort Weight). Just have a small load of your ID (whatever that identify each row) and HistTab.RefDate with Order By HistTab.RefDate Desc or Asc then rename the field (left join it to your FACT table) the use the new field in your FirstSortedValue (Sort Weight).

Re: FirstSortedValue and AGGR

Fri, 11 Dec 2015 15:28:48 GMT

RefDate is already Numeric; I just use "Date(RefDate,'MM/DD/YY hh:mm) to display it in readable form, as opposed to:

Don't see why I should need to add RANK to get a numeric result; it already is numeric.