15 Replies Latest reply: May 2, 2014 2:27 AM by Manish Kachhia RSS

    Sum of Top numbers calculated in expressions


      Hello All,

       

      I have a column "Cost" using calculated numbers from "Expressions". In the table, I have more than 100 rows, while I want to show only top 20 of them and also the sum in the first row.

       

      I know the sum of top N expression. But in that case, in the expression,  I can only use available fields but not calculated fields, right?

       

      If in the "Presentation", I pick "Max Number" as 20, it will give me top 20 rows, but the sum is stil the total of all the rows.

       

      Then how do I do that then?  Thank you

        • Re: Sum of Top numbers calculated in expressions
          anbu cheliyan

          Modify your dimension to below and select "Suppress when value is null"

           

          =If(Aggr(Rank(Your_expression) <= 20,Dim),Dim)

          • Re: Sum of Top numbers calculated in expressions
            Sergey Makushinsky

            Hi Sindy,

             

            Try something like this Sum({<You_DIMENSION= {"=Rank(SUM(Cost))<=20"}>}Cost)

             

            Or share a small example of your application

            • Re: Sum of Top numbers calculated in expressions
              Junya Fujiyoshi

              Below is the alternative idea.

              =aggr(if(rank(total sum(Cost))<=20,Dimension),Dimension)

              • Re: Sum of Top numbers calculated in expressions
                Manish Kachhia

                It is not clear from your question that which TOP 20 numbers you want to show.

                i.e. Based on Rank or Based on Script Load (First 20)

                 

                You can use Calculated Dimension to get the desired result but accurate help can be given if you provide sample data file along with your desired result... rather than we just guess what you want !!

                 

                Thanks

                  • Re: Re: Sum of Top numbers calculated in expressions

                    This is the small example.

                     

                    I wanted to show only top 5 of total cost, and also the sum at the first row. As you can see the total cost already has an expression with it.

                     

                    And sorry for confusion of my account. Something wrong with my old account "sindy" so I have to create new one.

                     

                    Thank you for your help.

                      • Re: Sum of Top numbers calculated in expressions
                        Manish Kachhia

                        Create a Straight Table

                         

                        Dimension

                        1) Calculated Dimension

                        =IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)

                         

                        2) [Extra Cases]

                         

                        Tick Suppress When Value is Null

                         

                        Expression

                        1) Cost2

                        [Extra Cases]*[Handling cost 2 per extra case]

                         

                        2) Cost1

                        [Extra Cases]*[Handling cost 1 per extra case]

                         

                        3) Total Cost

                        [Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])

                         

                        In Total Mode for all expression : Select Sum of ROWS

                         

                        Hope this helps....

                          • Re: Sum of Top numbers calculated in expressions

                            Manish, this works! It's awesome! Thank you very much!

                             

                            Also, if for each item, I have different total cost because "extra cases" is different, then the result will be top 5 items with top total of (total cost) of that item, is that correct?

                             

                            ex.

                            Item     Extra Cases     Total cost

                            1          5                         ...

                            1          8                         ...

                            2          3                          ...

                            2          5                           ...

                            2          9                           ...

                             

                            BTW, is it possible for you to break down the formula and kind explain what it means?

                             

                            =IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)



                              • Re: Sum of Top numbers calculated in expressions
                                Manish Kachhia

                                I hope you have marked Suppress When Value is NULL for Both Dimensions in Dimension Tab

                                 

                                =IF(Aggr(Rank(SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case])),4),Item)<=5,Item)


                                Let me break this, so that easy to understand


                                1) SUM([Extra Cases]*([Handling cost 1 per extra case]+[Handling cost 2 per extra case]


                                Same as your below expression. Just changed mathematically

                                SUM([Extra Cases]*([Handling cost 1 per extra case]

                                +

                                SUM([Extra Cases][Handling cost 2 per extra case]


                                Consider that above formula is Result1


                                2) Rank(Result1,4)

                                We can simply take Rank(Result1) but when there are more than two sum is equal, it will give you result of Rank as 2-3, or 4-5 etc. Anyway this is not important here as you are not looking for Rank to be displayed.

                                So you can use either or Rank(Result1,4) or Rank(Result1)


                                3)Aggr(Rank(Result1,4),Item)

                                In calculated Dimension, we are looking for TOP Items, so the above formula has to be aggregated to Item

                                 

                                4) IF(Aggr(Rank(Result1,4),Item)<=5,Item)

                                Now using IF, we are checking which are the items are having Rank less than or equal to 5 and displaying the same.

                                Here If will give you two results    

                                     a) Items having Top 5 ranks

                                     b) items which are not having top 5 ranks. For them we haven't displayed any thing so they will appear as - or

                                         null and hence we ticked Suppress When Value is NULL

                                 

                                Hope this helps..