6 Replies Latest reply: Dec 30, 2013 9:06 AM by Gysbert Wassenaar RSS

    Group by and sum

      All,

       

      I have this kind of table box:

       

      Portfolio Revenue

      P1         10

      P2          5

      P2          12

      P2          10

      P3          1

      P3           5

      ...

       

      i would like to easily obtain

       

      Portfolio Revenue

      P1         10

      P2          27

      P3          6

      ...

       

      how can i obtain such result ?

       

      thanks

        • Re: Group by and sum
          Gysbert Wassenaar

          Add a straight table chart object with Portfolio as dimension and sum(Revenue) as expression.

          • Re: Group by and sum
            Bill Markham

            Hi

             

            Try using a Chart of type Straight Table with :

             

                 Dimension          Portfolio

                 Expression        Sum(Revenue)

             

             

            Best Regards,     Bill

              • Re: Group by and sum

                thanks

                 

                i obtain the same if i add a calculated dimension with aggr(sum(Revenue), Portfolio)

                 

                what is actually the difference between both ?

                 

                Also: how to now filter the sum on another field, ex i wish to sum only the revenues corresponding to year 2011 ?

                 

                thanks

                  • Re: Group by and sum
                    Bill Markham

                    Hi

                     

                    Calculated Dimensions are worth avoiding unless absolutely needed as they are  resource hungry & inefficient.

                     

                    If you select Year as 2011 then it should work as you wish, do you have reason to suspect it does not ?

                     

                     

                     

                    Best Regards,     Bill

                      • Re: Group by and sum

                        thanks

                         

                        i wish to sum only revenues that are for 2011

                         

                        Portfolio   Rev 2011

                        P1           10

                        P2           11

                        ...

                         

                        if tried sum(IF(year = 2011, Revenue))

                         

                        but this gives me all zeros, whereas shuld be non 0

                      • Re: Group by and sum
                        Gysbert Wassenaar

                        A calculated dimension will perform worse than an expression. On small data set you may not notice this. If possible use an expression.

                         

                        You can add a listbox for the year field and select 2011 in it. The straight table will automatically be update to reflect your selection. If you want to fix the expression to 2011 and make it independent of selections in the year field you can use a set analysis expression: sum( {<Year={2011}>} Revenue). And make sure to use exact case sensitive field names. Because year is not the same as Year.