topic summing across mulitple variables in QlikView

summing across mulitple variables

Fri, 04 May 2012 16:59:53 GMT

I have a table that has the following columns:

Date	ID	Count	%
5/3	31	5	x
5/3	32	10	y
5/4	31	10	z
5/4	32	15	u

I would like to sum the Count column for each combination of Date and ID so I can see the % of that day and ID that set makes up.

I want:

x = 5 / (5 + 10) = 33.33%

y = 10 / (5 + 10) = 66.66%

z = 10 / (10 + 15) = 40%

u = 15 / (10 + 15) = 60%

How do I set up the % column to do this?

Fri, 04 May 2012 17:09:05 GMT

Try using

sum(total Aggr(count(field),Date))

Fri, 04 May 2012 17:30:41 GMT

I tried using the following:

sum(Counts)/sum(Aggr(sum(Counts),Date,ID))

This worked, but it only gave a % for the largest Count for each Date.

So in my example above:

x is NULL

y = 66.66%

z is NULL

u = 60%

How can I get the % to work for all rows?

Fri, 04 May 2012 17:34:05 GMT

What is the result of the expression:

Aggr(sum(Total Counts),Date)

CELAMBARASAN — Fri, 04 May 2012 17:34:14 GMT

Hi,

Check with this

sum(Counts)/sum(Total<Date> Counts)

Celambarasan

Fri, 04 May 2012 17:51:48 GMT

For the example I gave, sum(Counts)/sum(Total <Date> Counts) worked

When I added another variable to the mix I then wanted to use:

sum(Counts)/sum(Total <Date,ID> Counts)

which allowed me to sum across Date and ID and Disposition