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: 
sonikajain
Partner - Contributor
Partner - Contributor

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

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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   

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sonikajain
Partner - Contributor
Partner - Contributor
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   Have a look at the attched example.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sonikajain
Partner - Contributor
Partner - Contributor
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sonikajain
Partner - Contributor
Partner - Contributor
Author

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.

Please advise.