6 Replies Latest reply: May 18, 2012 3:24 AM by Sonika Jain

# expression for sum of  top 5 rows

Dear all,Please help me on wrting expression for sum of top 5 rows. I have Division and Balance.

AM using  if(Division = 'Sum of top 5 divisions', sum(if( rank(aggr( sum(BALANCE),DIVISION),4)<6,)...but this is not working..Please help me with the expressions

Thanks,

Sonika

• ###### expression for sum of  top 5 rows

Hi,

Try this

sum( {\$<DIVISION = {"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>}BALANCE)

Here vTop is a variable which will have the top N value.

So what we are saying in this expression is "Give me DIVISION who are top N according to the BALANCE and then Give the sum of BALANCE for only those DIVISIONs.

Hope this will help.

Regards,

Kaushik Solanki

• ###### expression for sum of  top 5 rows

Hi Kaushik.

rank(aggr(sum(BALANCE),DIVISION gives rank(number), we are trying here to compare it with Division (<DIVISION = {"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>}), so nothing is getting calculated .Please help me in writing the expression

• ###### Re: expression for sum of  top 5 rows

Hi,

Have a look at the attched example.

Regards,

Kaushik Solanki

• ###### expression for sum of  top 5 rows

Awsome Kausik..!!!..its really giving desired results.....

am not able to understand the meanign of putting rank inside "=" though...can u please explain..just if not inconvenient....

• ###### Re: expression for sum of  top 5 rows

Hi,

Sonika.

Let me explain you how the expression works.

sum( {\$<DIVISION = {"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>}BALANCE)

In this expression we are saying that first calculate the rank of the DIVISION as per the BALANCE by

=rank(aggr(sum(BALANCE),DIVISION))

Now this will be filtered by your varibale. So the expression

{"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>}

will give you the top n DIVISION

and then you are saying that you need to consider only this DIVISION at

{\$<DIVISION = {"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>}

and in full expression you are saying that give me sum of the BALANCE for only those top DIVISION's

Hope this is clear

Regards,

Kaushik Solanki

• ###### expression for sum of  top 5 rows

Hey Kaushik..Thanks a lot for this...

How I percieved it was that..

rank(aggr(sum(BALANCE),DIVISION)) returns a number (rank)..and we were comparing it to Division. I had no idea that {"=rank(aggr(sum(BALANCE),DIVISION))<= \$(vTop)"}>} will return DIVISION in place of rank....but this is working.

can u please help me with another problem http://community.qlik.com/message/220561#220561..nmy data model is concatenated..so cannot change it to association or join. we can add some tables if required though.