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.

     

    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

     

     

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