Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
erichshiino
Partner - Master
Partner - Master

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

View solution in original post

8 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

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
Partner - Master
Partner - Master

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

Please, check my app

disqr_rm
Partner - Specialist III
Partner - Specialist III

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

Please post your feedback.pic.png

Not applicable
Author

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
Contributor III
Contributor III

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
Partner - Master
Partner - Master

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

Not applicable
Author

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!

Not applicable
Author

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