Set Analysis with Count

[semgem12]

Hello

Hope someone can help please.

I have a table that has part numbers and for each part number it has the total stock available, e.g.

PartNumber, Stock

100001,200

100002,150

100003,320

etc.

In Qlik Sense I want to create a number of text boxes that show various counts. They are all very similar and show the number of parts that have a stock count between one value and another. E.g. the number of parts that have a stock count between 100 and 200.

So far I have this,

=count({<PartNumber = {"=sum(Stock) >100"} >} distinct PartNumber)

which counts the number of parts with stock over 100.

But how do I change this to say >100 and < 200? Or is there another way to achieve the same thing?

Thanks.

[devarasu07 · Accepted solution]

Hi,

Try like below

=count({$<Stock = {">100<200"}>} distinct PartNumber)

to show in text with custom string value like below

='# of parts stock count between 100 & 200 is :'& chr(10)& count({$<Stock = {">100<200"}>} distinct PartNumber)

[sunny_talwar]

May be this

=Count({<PartNumber = {"=Sum(Stock) >100 and Sum(Stock) < 200"}>} distinct PartNumber)

[semgem12]

Thank you! That has worked fine.

[semgem12]

Thank you but unfortunately that has not worked. It returns zero.