topic Sum distinct value in QlikView

Sum distinct value

Wed, 11 Nov 2015 19:40:51 GMT

Hello,

I was wondering if anyone can help me.

My data base looks something like this:

Trip	Product	Ammount
1	1	455
1	2	455
1	3	455
2	4	482
2	5	482
3	6	4323
4	7	123
5	8	87
5	9	87
6	10	123

My first column is the number of trip, and the Ammount is the total ammount from that trip. This value repeats itself for the same trip number.

Im currently tying to add the total ammount, the problem is that if I sum(Ammount) i would get the duplicated values and my result would be wrong. But if i use sum(Distinct Ammount) it wouldnt work because some Ammounts are repeating even tho its not the same trip.

Is there a way i can use something like sum(Ammount) for every distinct trip???

thanks for the help,

Re: Sum distinct value

sunny_talwar — Wed, 11 Nov 2015 19:46:30 GMT

Try this:

Sum(Aggr(Only(Ammount), Trip)) or

Sum(Aggr(Avg(Ammount), Trip)) or

Sum(Aggr(Sum(DISTINCT Ammount), Trip))

Re: Sum distinct value

anlonghi2 — Wed, 11 Nov 2015 20:14:34 GMT

H Mariana,

try using the following expression on a straight table with Trip and Product as dimensions.

f(dimensionality()=0,Sum(Aggr(Sum(DISTINCT Ammount), Trip)) ,sum(Ammount))

Best regards

Andrea