topic Help with FirstSortedValue() and Aggr() in QlikView

Help with FirstSortedValue() and Aggr()

Fri, 02 May 2014 16:15:40 GMT

Hi all,

I want to use the Aggr() function inside of FirstSortedvalue(). The only way I've ever used FirstSortedValue is on static values in a field. I'm not sure if what I want to do is possible. See below expression:

=FirstSortedValue(POSupplierNameClean, Aggr(if(Avg(PODate - PODateApproval) < 0, 0, Avg(PODate - PODateApproval)), POSupplierNameClean))

I want to average the difference in PODate and PODateApproval ("Avg. PO Days"), grouped by supplier. Once I have that average, I want to pick the supplier with the largest "Average PO Day." The expression is not calculating.

Re: Help with FirstSortedValue() and Aggr()

MK_QSL — Fri, 02 May 2014 16:31:11 GMT

If possible, please provide sample data.

FirstSortedValue + Aggr >> A very good video available on QlikShare.com

Try to search by Aggr...

Re: Help with FirstSortedValue() and Aggr()

Fri, 02 May 2014 16:37:46 GMT

Sorry, I forgot to mention that this is in a text box - so there is no dimension variable. I'm not able to provide sample data for now, but the above expression should be sufficient support. If anyone notices syntax that looks incorrect, please let me know.

I tried:

Max(Aggr(if(Avg(PODate - PODateApproval) < 0, 0, Avg(PODate - PODateApproval)))

and this returns the correct value. Once I add the FirstSortedValue with SupplierNameClean, it returns Null.

Re: Help with FirstSortedValue() and Aggr()

MK_QSL — Fri, 02 May 2014 16:48:03 GMT

Max(Aggr(if(Avg(PODate - PODateApproval) < 0, 0, Avg(PODate - PODateApproval)))

The highlighted part may be something like customername or suppliername or some other field

Re: Help with FirstSortedValue() and Aggr()

Fri, 02 May 2014 16:51:31 GMT

I figured it out. The expression needed the DISTINCT keyword in case there were multiple suppliers with the same value, as shown below:

=FirstSortedValue(DISTINCT POSupplierNameClean, Aggr(if(Avg(PODate - PODateApproval) < 0, 0, Avg(PODate - PODateApproval)), POSupplierNameClean))