Duplicate problem with alt-statement

[#1]

Hi

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

[#2]

try this

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

[#3]

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.

[er_mohit · Accepted solution]

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

[#5]

Great! the first one did work! Many thanks!

-Janne