topic Re: how to sum duplicate value ? in QlikView

how to sum duplicate value ?

Wed, 10 Jun 2015 08:00:36 GMT

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 :

sales

100,

100

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

Re: how to sum duplicate value ?

qlikmsg4u — Wed, 10 Jun 2015 08:39:16 GMT

Getting right

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

Re: how to sum duplicate value ?

Wed, 10 Jun 2015 08:48:31 GMT

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

Re: how to sum duplicate value ?

awhitfield — Wed, 10 Jun 2015 08:54:59 GMT

HI Cecile,

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

Regards

Andy

Re: how to sum duplicate value ?

PrashantSangle — Wed, 10 Jun 2015 09:05:46 GMT

Hi,

Try below two expression

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

sum(Sales)

Regards

Re: how to sum duplicate value ?

Wed, 10 Jun 2015 09:19:04 GMT

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 !

Re: how to sum duplicate value ?

Wed, 10 Jun 2015 09:20:12 GMT

HI..

It is giving right answer..

Re: how to sum duplicate value ?

Wed, 10 Jun 2015 09:21:49 GMT

hello,

thanks a lot with the aggregate function it works !!

Re: how to sum duplicate value ?

PrashantSangle — Wed, 10 Jun 2015 09:24:25 GMT

Hi,

Welcome.

Mark appropriate reply as correct and close the thread.

Regards

Re: how to sum duplicate value ?

Wed, 10 Jun 2015 09:32:18 GMT

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