8 Replies Latest reply: Mar 16, 2012 6:48 AM by Lav Jain

# How to sum all the values in a column

My table looks like this:

HouseCatsDogs
% Total of Cats (PTC)
Dog/Cat Ratio (CDR)
WAC
-4535100%- .7711
House 1102022%2.44
House 2151033%.67.2211
House 320544%.25.11

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

HouseCats
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!

• ###### How to sum all the values in a column

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.

• ###### Re: How to sum all the values in a column

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

• ###### Re: How to sum all the values in a column

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

• ###### Re: How to sum all the values in a column

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.

• ###### Re: How to sum all the values in a column

Hi,

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

```sum(total {<House=>} Dogs)/sum(total {<House=>} Cats)
```
• ###### Re: How to sum all the values in a column

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))

• ###### Re: How to sum all the values in a column

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!

• ###### Re: How to sum all the values in a column

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