topic Problems with "=count(if(...))" function in QlikView

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

Wed, 12 Oct 2011 09:48:36 GMT

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

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

Wed, 12 Oct 2011 09:56:12 GMT

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.

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

Sokkorn — Wed, 12 Oct 2011 10:13:23 GMT

Hi Bjarne,

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

Regards,

Sokkorn

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

Wed, 12 Oct 2011 10:13:23 GMT

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!!

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

Wed, 12 Oct 2011 11:14:27 GMT

Hi Bjarne,

A correction in my original suggestion, please see below:

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

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

rohit214 — Wed, 12 Oct 2011 11:15:00 GMT

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

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

Wed, 12 Oct 2011 11:23:38 GMT

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.

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

rohit214 — Wed, 12 Oct 2011 11:30:47 GMT

hey

try this

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

thnaks

rohit

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

Wed, 12 Oct 2011 11:32:22 GMT

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.

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

Wed, 12 Oct 2011 11:36:13 GMT

Hello,

this should give you the right result:

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

Regards,

Lukas

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

Wed, 12 Oct 2011 11:43:47 GMT

Hi Lukas

Your answer is the same as Sokkorn and that gives 6 not 4.

Hi Rohit

I think you mean DCN.Diff.L or DCN.Diff.H and not twice DCN.Diff.W. But it works

Hi GJ

That works also.

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