topic Re: Sum, mode, count question in QlikView

Sum, mode, count question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Trying to count the number of sizes; however, I do not want to include in the count the sizes that doesn't come in a particular size. The image below shows a dress that comes in numeric sizing; however, the count function is counting the zeros in sizes XS-L. Is there a syntax, similar to the mode function that'll allow me to count only the inventory within the sizes most common of a number or a letter? The count I need is 8, not 12. If size "6" showed zero, then my count would be 8 with 1 count with zero inventory.

Re: Sum, mode, count question

lorenzoconforti — Fri, 20 Oct 2017 20:35:41 GMT

Maybe something like this?

Count(if( IsNum(Size),Size))

Can you post your application?

Re: Sum, mode, count question

Anonymous — Fri, 20 Oct 2017 20:49:47 GMT

Post my application? I use Qlik Sense...

Below is what I get using your "Count(if(IsNum(Size),Size))".

Re: Sum, mode, count question

lorenzoconforti — Fri, 20 Oct 2017 21:20:50 GMT

I see; I got confused as this is actually the QlikView forum. I think you can still upload your qvf file.

Probably best to re-post on the QlikSense forum

Re: Sum, mode, count question

effinty2112 — Sat, 21 Oct 2017 09:49:31 GMT

Hi Karley,

Try:

Count(if(IsNum(ItemSize),ItemSize))

Cheers

Andrew

Re: Sum, mode, count question

swuehl — Sat, 21 Oct 2017 15:57:52 GMT

Maybe something like

=Count( DISTINCT If( Isnum(ItemSize),ItemSize))

or using set analysis:

=Count({<ItemSize = {"=Isnum(ItemSize)"}>} DISTINCT ItemSize)

=Count({<ItemSize = {">0"}>} DISTINCT ItemSize)

If this doesn't work, would be good to see your expression that shows the 12 total count.

Re: Sum, mode, count question

uroboros — Sat, 21 Oct 2017 16:47:31 GMT

I agree with your contribution