Problems with "=count(if(...))" function

[#1]

Hi All

I have a problem with an count if function. I use this formula

=count (if (DCN.Sum_Diff>0,DCN.Sum_Diff))

And the result is 6, but as you can see in the tabe below the result is suppose to be 4. The two articles in the top is both counting 2 and not 1. Anyone have the same experinces with that? And has a solution for that?

Article	DCN.Site	DCN.Diff.L	DCN.Diff.W	DCN.Diff.H	DCN.Sum_Diff
3918201	C690	1	1	0	2
5909002	C690	1	1	0	2
8260001	C690	1	1	0	2
36027033	C690	0	1	1	2

This formula is working fine for site C190 and C290 that has data in the same WMS system (called ANT). Data for site C690 is coming from SattStore. I do not know if that is a problem in this issue.

I have the same problem in this formula. The result is 5582 but should be 2950. Can you maybe also solve that?

=sum(if(DCU.SKU-DCN.SKU=0,if(RangeMax(DCU.Length,DCU.Width,DCU.Height)=RangeMax(DCN.Length,DCN.Width,DCN.Height)and RangeAvg(DCU.Length,DCU.Width,DCU.Height)=RangeAvg(DCN.Length,DCN.Width,DCN.Height) and RangeMin(DCU.Length,DCU.Width,DCU.Height)=RangeMin(DCN.Length,DCN.Width,DCN.Height) and DCU.Volume-DCN.Volume=0 and DCU.Weight-DCN.Weight=0,0,1)))

Hope for some help.

Best regards

Bjarne

[#2]

Hi There,

You could try the below expression, maybe it will produce the desired result:

     count(distinct if( DCN.Sum_Diff>0 and DCN.Sum_Diff = 1, Article))

Hope it helps.

Regards GJ.

[Sokkorn]

Hi Bjarne,

Try this =sum (if (DCN.Sum_Diff>0,1,0))

Regards,

Sokkorn

[#4]

Hi GJ

Your suggestion gives me 0 in result

Hi Sokkorn

Your suggestion gives me still 6 in the result.

It is counting something I just can't find out what!!

[#5]

Hi Bjarne,

A correction in my original suggestion, please see below:

     count(distinct if( DCN.Sum_Diff>0 and DCN.Diff.W = 1, Article))

[rohit214]

hi bjarne

try this expression

=count(if (DCN.Sum_Diff>0,DCN.Sum_Diff,0)

hope it may helps you,please let me know.

thanks &regard

rohit

[#7]

Hi Rohit

That expression gives the result 7369 which is eqaul to the total amount of articles I think.

Hi GJ

That gives me the result 4, but I think it is a limited solution since DCN.Diff.W will not always be 1. It could be = 0 and then DCN.Diff.L and DCN.Diff.H could both be 1.

[rohit214]

hey

try this

count(distinct if( DCN.Sum_Diff>0 and DCN.Diff.W = 1or DCN.Diff.W = 0 , Article))

thnaks

rohit

[#9 · Accepted solution]

Hi Bjarne,

I see how that can be limited, what about:

     count(distinct if( DCN.Sum_Diff>0 , Article))

This will remove the limitations and might produce the correct results.

Regards GJ.

[#10]

Hello,

this should give you the right result:

=sum(if (DCN.Sum_Diff>0,1,0))

Regards,

Lukas