Help with FirstSortedValue() and Aggr()

[#1]

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.

[MK_QSL]

If possible, please provide sample data.

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

Try to search by Aggr...

[#3]

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.

[MK_QSL]

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

[#5 · Accepted solution]

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))