Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to find out city for which I have maximum Applications. Each application has a unique id : AppId. My expression returns null.
Expression:
vFirstCity=FirstSortedValue (City,Aggr(Count(AppId),City ))
For max you will need a negative sign next to the Aggr function:
vFirstCity=FirstSortedValue(City, -Aggr(Count(AppId), City))
For max you will need a negative sign next to the Aggr function:
vFirstCity=FirstSortedValue(City, -Aggr(Count(AppId), City))
Thanks a lot for quick revert.Extremely silly mistake.Anyway to get any value if there are multiple cities with same count ?
Hi,
Yes for Maximum value,minus sign should be used.
vFirstCity=FirstSortedValue(City, -Aggr(Count(AppId), City))
if you get multiple values then FirstSortedValue() will return null.
You can use a Concat() function to see them all:
=Concat(Aggr(FirstSortedValue(City, -Aggr(Count(AppId), City)), City), ', ')
Hi,
Here is an extract from the Reference Manual. Hope this helps.
firstsortedvalue ( [ distinct ] expression [, sort-weight [, n ]] )
returns the first value of expression sorted by corresponding sortweight
when expression is iterated over a number of records as
defined by a group by clause. 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 the word distinct occurs before the
expression, all duplicates will be disregarded.
You can choose a second criteria, dividing by a large number
FirstSortedValue(City, -Aggr(Count(AppId), City) + count(otherfield)/10000 )
That makes sense. Thanks.