is it possible, in the concat function, to use an expression in the sort-weight parameter ?
concat( Distinct MyField, '|') works perfectly.
the string is sorted in an alphabetical order.
I would like to sort my field on an expression like Sum(Measure) ....
Solved! Go to Solution.
acc. to the manual, concat takes a third parameter for sort (should evaluate to numeric value, sort order is ascending.
So, without trying, a
concat (Distinct MyField, '|', sum(measure) )
might already do the trick.
Thank you four your answer.
this exactly what i've done, but it doesn't work.
It seems that the third paramater (sort-weight) must be a field and not an expression.
with the FirstSortedValue Function, i can get a field after sorting on an expression, but only the first one ...
I noticed a thread answer by Johannes (http://community.qlik.com/message/75544#75544) where the help file stated
The function argument expressions must not contain aggregation functions,
unless these inner aggregations contain the total qualifier.
For more advanced nested aggregations, please use the aggr function in
combination with calculated dimensions.
Though I couldn't find that passage in my help to topic concat .
And indeed, sum( total Amount) as sort weight is a valid expression, but of course does not give a useful sort order.
So I tried with something like
=concat( Distinct Salesman, ',' , -aggr( sum( Amount), Salesman))
which looks promising, but results in first an alphabetical ordered list of Salesman, then sum(amount) ordered list in one concatted string.
Strange, have to think about for another while.
For the moment, I added a fix with a right statement to get only the list of Salesman ordered by sum(amount).
This seems to work, though it looks a bit ugly.
Find attached my sample.