Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Reply
MK_QSL
MVP
MVP

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