Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
khan_fayez
New Contributor III

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
MVP
MVP

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

Community Browser