1 Reply Latest reply: Jul 3, 2017 3:30 PM by Manish Kachhia RSS

    Aggr & Rank Function

    Fayez Khan

      Hi,

      I want to get top spender YTD. Now When I am using:

       

      =only(
      if(
                         
      aggr(
                                  
      Rank(
                                              
      sum({<Year= {"$(=max(Year))"},MonthNo={"<= $(=max(MonthNo))"}>}
                                                 ApprovedAmountPerAttendee)
                                         )
      ,
      [Employee Name])
      =1,  
      [Employee Name]) )  ,

       

      I am getting the correct results.

      What I am not able to understand is that when I use :

       

      =only(
      if(

                       
      rank(
                                     
      aggr(
                                               
      sum({<Year= {"$(=max(Year))"},MonthNo={"<= $(=max(MonthNo))"}>}
                                                ApprovedAmountPerAttendee)
                                   ,
      [Employee Name]),
                         1,2)         =1,
      (
      [Employee Name])
      )
      )

      I am getting the nested aggregation error. My understanding is that we do group by first and then rank the grouped by data.

      Can any body explain this to me. It will help me to clear the concepts.

       

      Thanks.

        • Re: Aggr & Rank Function
          Manish Kachhia

          If you want to use Aggr first and then Rank then you need to use Aggr twice.

           

          =Only(Aggr(IF(Rank(Aggr(sum({<Year= {"$(=max(Year))"},MonthNo={"<= $(=max(MonthNo))"}>}
                                                    ApprovedAmountPerAttendee) ,[Employee Name]),1,2)=1,[Employee Name]),[Employee Name]))