topic Re: Group by and sum in QlikView

Group by and sum

Mon, 30 Dec 2013 13:46:07 GMT

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 — Mon, 30 Dec 2013 13:49:44 GMT

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

Re: Group by and sum

Anonymous — Mon, 30 Dec 2013 13:50:40 GMT

Try using a Chart of type Straight Table with :

Dimension Portfolio

Expression Sum(Revenue)

Best Regards, Bill

Re: Group by and sum

Mon, 30 Dec 2013 13:57:07 GMT

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

Anonymous — Mon, 30 Dec 2013 14:01:53 GMT

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

Mon, 30 Dec 2013 14:04:19 GMT

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 — Mon, 30 Dec 2013 14:05:18 GMT

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.