FirstSortedValue Function with Aggr

    Hello All,


    Using FirstSortedValue and Aggr functions we can found the specific string from our data set.

    Let's start with a simple example.






    Now I want to found which country has highest sales? For this, We can use FirstSortedValue with Aggr function.



    Let's understand the expression: 

    1. Aggr(Sum(Sales), Country): Finding Sum Sales aggregated by Country.
    2. FirstSortedValue([Country], -aggr(Sum9Sales),[Country]),1): Now We need the country name based on higest sales. So, We've used Country field in FirstSortedValue function.
      • Minus(-) Sign: if we did not put minus sign then upper expression give lowest sales country name.
      • Number (rank): put your desired number rank in the last.


    So Expression gives answer: Canada

    Some More Example:

    1. FirstSortedValue([Country],-aggr(Sum([Sales]),[Country]),2)

               Answer : India

    2. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),1)  //Remove Minus sign

              Answer: Japan

    3. FirstSortedValue([Country], aggr(Sum([Sales]),[Country]),2)  //Remove Minus sign

              Answer: China



    This document was generated from the following discussion: FirstSortedValue Function with Aggr