Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))