topic using Aggr to sum distinct values in QlikView

using Aggr to sum distinct values

Thu, 16 Sep 2010 10:42:51 GMT

I need to total a price column on orders as follows:

id	salesman	price
1	tom	100
1	jim	100
2	jim	1
2	sam	1
3	tom	100
3	bob	100
3	joe	100
		201

ie when totalling we should only sum the price once per order. I tried using:

sum(aggr(price,id)), which does the correct sum but it look like this:

id	salesman	price
1	tom	100
1	jim	0
2	jim	1
2	sam	0
3	tom	100
3	bob	0
3	joe	0
		201

The sum is correct but I dont want it to zero out the prices like that. Any ideas what formula i can use to make it look like the first table?

Thanks

using Aggr to sum distinct values

Thu, 16 Sep 2010 11:57:26 GMT

I think this works, so that aggr is only used in the total and doesnt affect the displayed prices in the table:

if(dimensionality()=0,sum(aggr(price,id)),price)

Anyone got a better solution?

using Aggr to sum distinct values

johnw — Fri, 17 Sep 2010 00:29:20 GMT

Possibly sum(aggr(nodistinct price, id),price)), but I bet it messes up the total. If so, no, I'm not thinking of a better solution than yours right now.

using Aggr to sum distinct values

vgutkovsky — Fri, 17 Sep 2010 06:24:56 GMT

See attached solution.

Regards,

using Aggr to sum distinct values

Fri, 17 Sep 2010 09:12:52 GMT

Nice one, thanks.