How to sum all the values in a column

[#1]

My table looks like this:

House	Cats	Dogs	% Total of Cats (PTC)	Dog/Cat Ratio (CDR)	WAC
-	45	35	100%	-	.7711
House 1	10	20	22%	2	.44
House 2	15	10	33%	.67	.2211
House 3	20	5	44%	.25	.11

The calculations I'm using are as follows:% Total Cats (PTC)

House	Cats	Dogs	% Total of Cats (PTC)	Dog/Cat Ratio (DCR)	WAC
-	sum([Cats])	sum([Dogs])	sum(Sum([Cats]) / (sum(total [Cats])))	-	**need to figure out how to sum these values and re-use that value**
[House]	[Cats]	[Dogs]	Sum([Cats]) / (sum(total [Cats]))	[Cats] / [Dogs]	(Sum([Cats]) / (sum(total [Cats]))) * ([Dogs] / [Cats])

Read WAC as total percentage of cats times dog/cat ratio.

What I need to do is figure out how to sum the values in the WAC column. I can't get QlikView to do that for me - in other words I can't get QlikView to calculate and show the .7711 value demonstrated above. Not only do I want to sum these values, but I want to re-use the sum value later on in another chart. So later on I want to multiply something by the .7711 value.

I'm sure this seems bizarre and maybe there's even an easy answer that I'm just missing. But I can't figure out how to write the expression to total the WAC values for me. Can someone please help? I sincerely appreciate any guidance you can provide. Thank you!

[disqr_rm]

You somehow need to wrap whole thing with a SUM(), then it will sum the column values.

Since you are using SUM() already, SUM() within a SUM() will not work.

Look into AGGR() functions and play with it a little. I am sure that would work.

What would help if you upload a sample document here.

[erichshiino]

Not sure, if I got you, but I think everything was simpler.

Please, check my app

[disqr_rm]

Also try selecting "sum" for "Total Mode" under Expression tab for the WAC expression.

Please post your feedback.

[#5]

This got my halfway there. It did make QlikView show the sum of the rows. However now I need to reuse that value elsewhere. Thanks for the ideas thus far - I sincerely appreciate your time and effort trying to help solve this matter.

[yasus]

Hi,

if you want to use this value in the same table try the following formula:

sum(total {<House=>} Dogs)/sum(total {<House=>} Cats)

[erichshiino · Accepted solution]

If you get the expression as it is, does it work?

I guess it depends where you use it, but you can write something like:

sum ( aggr( sum(Dogs)/suM(total Cats), House))

[#8]

This worked. That you for your tremendous help. I was stuck on this for a long time and can now continue with my project. Thank you so much!

[#9]

Hi,

i think you can solve my problem again.

i've dimemsions dim1,dim2,dim 3..in hierarchial format as in pivot table.

i've a value column in the table, as one expr....i'm showing sum(value) and i also have dimension due date....& if the due date <today() date then in the expr column Overdue Amt i simply show sum(value) else i'll show it the Not due column...this is fine....

now i've to find %Overdue & %Not due.....wich is Overdue Amt/sum(value) & Not Due Amt/sum(value) respectively...

i'm getting the % fine..for each row...but at the total level i dnt want to see sum of all % for Overdue & %Not Due

Bt only the sum of Overdue Amt/sum of Remainin Amt & same for %Not due as well

Plz help

stuck for 1 day cmplete

Regards