Group by and sum

[#1]

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

[Gysbert_Wassenaar]

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

[Anonymous]

Hi

Try using a Chart of type Straight Table with :

     Dimension          Portfolio

     Expression        Sum(Revenue)

Best Regards,     Bill

[#4]

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

[Anonymous]

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

[#6]

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

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