topic Re: Duplicate problem with alt-statement in QlikView

Duplicate problem with alt-statement

Tue, 21 May 2013 13:08:41 GMT

I have following script in expression.

=sum(if(alt([Billing Day],0)=0,([Order quantity_WMENG]*Net_Price/ExhRate,0))

For some reason this this formula sums everything twice and I can't see what is the problem. Would you have any ideas. The formula calculates properly without if(alt statement, but with that all results are multiplied with 2. There shouldn't be any duplicate rows in my data model; all these needed values are only once in my data model.

Many thanks already beforehand

Regards

Janne

Re: Duplicate problem with alt-statement

Tue, 21 May 2013 13:12:28 GMT

try this

=if([Billing Day]=0,sum(Distinct ([Order quantity_WMENG]*Net_Price) / ExhRate),0)

Re: Duplicate problem with alt-statement

Tue, 21 May 2013 13:15:11 GMT

That wouldn't work. The reason why I need to use "alt"-statement here is that I want to include onlu those rows into calculation where Billing day doesn't exist, so with the normal if formula I get nothing.

Re: Duplicate problem with alt-statement

er_mohit — Tue, 21 May 2013 13:16:01 GMT

Try your expression with distinct function

sum(distinct if(alt([Billing Day],0)=0,([Order quantity_WMENG]*Net_Price/ExhRate,0)))

if problem doesn't solve then try this

sum(distinct if([Billing Day]=0,([Order quantity_WMENG]*Net_Price/ExhRate,0)))

hope it helps

Re: Duplicate problem with alt-statement

Tue, 21 May 2013 13:19:07 GMT

Great! the first one did work! Many thanks!

-Janne