topic duplicates problem in sum in QlikView

duplicates problem in sum

Wed, 14 Mar 2012 09:46:38 GMT

hi all,

i have a problem in my chart, i did a chart that count and sum(like showing in the example i added).

the problem that in the "withdraw" expression the total is without duplicates, and in WD the total is with duplicates.

does anyone have an idea, i tried to put DISTINCT but it doesnt work.

i am adding my expression, if anybody have any idea.

=sum(aggr(if(sum({<YYYY=,MM=,DD=>} FTD)>0 and

sum(if(Aggr((sum(DEPOSITS) - sum(FTD)),USER_NAME)>0,1))=0 and $(mWithdraws)>0,$(mWithdraws),0),

RET_DESK,R_REPRESENTATIVE,USER_NAME,FTD_CAL.FULL_DATE,ACCOUNT_LANG))

thank you

eran

CELAMBARASAN — Wed, 14 Mar 2012 10:26:30 GMT

Hi,

Try Creating a flag for duplicates like

Load

Field1,

Field2,

If(Peek('Field1')=Field1 and peek('Field2')=Field2,1,0) as DuplicateFlag

From datasource;

Before this your data to be ordered.

Then Write expression

Without duplicates Sum({<DuplicateFlag={0}>} Field2)

With Duplicates simply use Sum(Field2)

Celambarasan

Wed, 14 Mar 2012 11:14:35 GMT

i understand what you mean but i dont know how to do it with my expression.