how to sum duplicate value ?

[#1]

Hello;

I have a problem in a pivot table, the total is wrong because I have two sames values in the field I want to sum.

For example :

customer ID :

A

B

C

sales

100,

100

50

the total should be 250 but in my pivot table there is 150 as if Qlikview considered the sales value as a duplicate value.

I tried to put in my expression sum(Distinct sales) but it doesn't change anything.

Can anyone help me ? Thanks in advance

[qlikmsg4u]

Getting right

While storing Qlikview stores duplicates only once, but in calculation it calculates all values. (Correct me if I am wrong.)

[#3]

Hi,

I saves all unique values once but the also store the frequency of the value meaning it will more or less be like 100*2+50.

/Hampus

[awhitfield]

HI Cecile,

if the above responses do not resolve you issue, please upload your qvw.

Regards

Andy

[PrashantSangle]

Hi,

Try below two expression

Sum(Aggr(sum(Distinct sales),CustomerID))

or

sum(Sales)

Regards

[#6]

Hello

Thanks for your reply but I don't think it calculates all value. For confidentiality reason, I can't upload my qvw file (beside I don't know how to do it in this message !). I exported my pivot tablefrom QVW  to excel and the result shown in QVW is 261 436 instead of 262 059 -> 624 value appears twice !

[#7]

HI..

It is giving right answer..

[#8]

hello,

thanks a lot with the aggregate function it works !!

[PrashantSangle]

Hi,

Welcome.

Mark appropriate reply as correct and close the thread.

Regards

[#10]

Hi Cecile,

this I think will be down to your underlying data model.

Your underlying fact table table will contain the value 624 as a single row.

In your dimension table the join on that fact row is to two separate material numbers, as you can see (31344,31348).

When you bring this dimension field into a pivot table, QV will correctly show you the 624 value against both possible material numbers.

However the total value you see is for the expression total which will only ever total the actual fact values you have. As the 624 fact row is only in there once, that's what is used for the total.

At least I believe that is what your issue is and 262059 is an inflated value of your true costs, which I wouldn't is correct

hope that helps

Joe