0 Replies Latest reply: Jul 6, 2018 2:47 AM by ishan Bhatt RSS

    FirstSortedValue Function with Aggr

    ishan Bhatt

      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 of 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 the lowest sales country name.
        • Number (rank): put your desired number rank in the last.


      So Expression gives the 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