0 Replies Latest reply: Oct 13, 2017 1:03 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.

       

      Sales:

       

      CountrySales
      US2000
      China1500
      India2500
      Japan1000
      Canada4000

       

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

      !

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


      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