Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

FirstSortedValue question

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

1 Solution

Accepted Solutions
Highlighted
Not applicable

I got it

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

View solution in original post

5 Replies
Highlighted
Specialist II
Specialist II

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!

Highlighted
Not applicable

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

Highlighted
Specialist II
Specialist II

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

Highlighted
Not applicable

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

Highlighted
Not applicable

I got it

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

View solution in original post