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

# Aggr & Rank Function

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

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]))