6 Replies Latest reply: Dec 21, 2015 4:14 AM by Andrew Walker RSS

    Lookup Name of a Max Aggr Count

    Travis Lyon

      Hello, I'm new to the Forums. I'm trying to find out if there is a way / how to get the associated value from a Max(Aggr(Count())) expression.

       

      Specifically, I have a data set with 4 regions. Each region has a different total number of territories.

      To begin, I'm using the formula max(aggr(count([Territory]),[Region])) to get the value of the region with the most territories.

       

      Example:

      Region A = 250 territories

      Region B = 50 territories

      Region C = 300 territories

      Region D = 100 territories

       

      So my formula returns 300.

       

      Now I'm trying to put this in a text box to say 'The Region with the most territories is [Region Name] with [Count of Territories] Territories.

      ex. "The Region with the Most Territories is Region C, with 300 Territories."

       

      What formula can i use to populate the [Region Name] in my above example?

      It's late, so hopefully there is an easy way and I'm just overthinking, but any help is much appreciated.

        • Re: Lookup Name of a Max Aggr Count
          Sunny Talwar

          To get that, you can use FirstSortedValue() function:

           

          =FirstSortedValue(Region, -Aggr(Count(Territory), Region))

          • Re: Lookup Name of a Max Aggr Count
            Andrew Walker

            Hi Travis,

            I know this question has been answered well by Sunny but I would like to offer an alternative to FirstSortedValue, an alternative which is a little bit harder to follow but has an advantage which, I think, makes it worthwhile knowing.

            I have a little problem with the FirstSortedValue function as it fails if there is tie for first place (or what ever place in the ranking you are looking for should you enter the appropriate perimeter).

            If your data set was this instead:

             

            Region Territories
            A250
            B50
            C300
            D

            300

             

            The FirstSortedValue would return a null value. An alternative is to use the function

             

            =Concat(Aggr(if(Rank(Sum(Territories),1,1)=1, Region),Region),',')

             

            which returns C,D in this case.

             

            I think this behaviour if there is a tie is a bit of a flaw in the FirstSortedValue function and it puts me off using it.

             

            Cheers

            Andrew

              • Re: Lookup Name of a Max Aggr Count
                Sunny Talwar

                Thanks for bringing this up Andrew. I guess there are variety of things that can happen which may need the solution to be altered. But I usually like FirstSortedValue because it is very straight forward for most of the instances except few where it chokes.

                 

                In addition to your proposed solution, I think the same can be done using Set analysis rather than the if statement

                 

                =Concat({<Region = {"=Rank(Sum(Territories),1,1)=1"}>}Region, ', ')


                or

                 

                =Concat({<Region = {"=Sum(Territories)=Max(TOTAL Aggr(Sum(Territories), Region))"}>}Region, ', ')

                 

                I hope you will continue to help me become more efficient