FirstSortedValue question

[#1]

hi,

I got expression

=FirstSortedValue(CustomerListID,(Aggr(sum({$<AccountType={'Income'}>}Transactions.Amount),CustomerListID)))

The intent of this expression is show me the customer with lowest total sales.

FIrstSortedValue returns NULL

but when I use min instead of sum

=FirstSortedValue(CustomerListID,(Aggr(min({$<AccountType={'Income'}>}Amount), CustomerListID))) then I get correct value returned....

What is going on here?

Saurabh

[jerem1234]

It may be that multiple CustomerListID's have the same lowest sum, in which case firstsortedvalue will return a null.

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

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

If this doesn't help, try to post an example that demonstrates your problem.

Hope this helps!

[#3]

That's helpful....especially this

"Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. "

But does it mean we can not sort by Dates...or Qlikview  will return the number representation of dates when it used in this function....or do we need to handle it our side...

[jerem1234]

Works on dates; it will take the number representation of the date (since dates are pretty much dual values).

[#5]

What is wring with it?

FirstSortedValue(Date(TxnDate), Aggr(Min({<AccountType={'Income'}>TxnDate),CustomerListID))

I want to get the minimum transaction date for a customer...but it does not work....

[#6 · Accepted solution]

I got it

FirstSortedValue(Date(TxnDate), Aggr(Min({<AccountType={'Income'}>TxnDate),CustomerListID, TxnDate))