topic sum total regardless of selection in QlikView

sum total regardless of selection

Fri, 18 Sep 2009 15:31:53 GMT

Hi All,

Cost Centre Sub Cost Centre Amount

10000 10000_XX 500

10000 10000_XY 500

10000 10000_XZ 500

20000 20000_XX 500

20000 20000_XY 500

20000 20000_XZ 500

Given a table like the above I need to add a column that will give me the total of Cost Centre for each line the cost centre appears on so for the above I would have 1500 for each line of 10000.

But there's a catch! I know I could use aggr to do this, however, there are other selections in list boxes external to this straight table that could restrict the list so that e.g. only 10000 sub cost centres XX and XY would appear, I need the column to still show 1500, I've found using aggr the total changes to 1000.

I've tried using set analysis but can't get it to work - this is what I tried:

=sum(total {$<[Cost Centre] = {[Cost Centre}>} Amount)

Please help!

Thanks.

sum total regardless of selection

Fri, 18 Sep 2009 17:22:37 GMT

sum({1} total <[Cost Centre] > Amount)

sum total regardless of selection

Fri, 18 Sep 2009 17:34:07 GMT

Thanks Richy. That nearly works, it gives me the total regarless of selection but when you select a cost centre it removes the Amounts from other rows but the rows remain? Thanks

sum total regardless of selection

Fri, 18 Sep 2009 17:47:00 GMT

I found the problem, added a simple if statement to fix it:

if(sum(Amount)=0,0,sum({1} total <[Cost Centre]> Amount))

Thanks again.