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

    expression for sum of  top 5 rows

    Sonika Jain

      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
          Kaushik Solanki

          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
              Sonika Jain

              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
                  Kaushik Solanki

                  Hi,

                   

                     Have a look at the attched example.

                   

                  Regards,

                  Kaushik Solanki

                    • expression for sum of  top 5 rows
                      Sonika Jain

                      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
                          Kaushik Solanki

                          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