topic Re: FirstSortedValue question in QlikView

FirstSortedValue question

Thu, 29 May 2014 19:14:57 GMT

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

Re: FirstSortedValue question

jerem1234 — Thu, 29 May 2014 19:39:39 GMT

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!

Re: Re: FirstSortedValue question

Thu, 29 May 2014 19:56:16 GMT

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

Re: FirstSortedValue question

jerem1234 — Thu, 29 May 2014 20:00:00 GMT

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

Re: Re: FirstSortedValue question

Thu, 29 May 2014 22:43:46 GMT

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

Re: Re: FirstSortedValue question

Thu, 29 May 2014 22:47:42 GMT

I got it

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