Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

New Contributor II

How can I count bins with a certain count of [field] minus [field]?

I need to count how many Bins have 0,1,2,3 and so on "Quants", excluding "Special Stock Num Count" quants. So, how many bins have 0,1,2,3 non-special quants, etc.

I have a table that is organized with the following columns: Storage Type, Description, 0,1,2,3, and so on. This is supposed to show me how many Storage Bins within each Storage Type have a Quant – Special Stock Num = 0, =1, =2, etc.

So I would think the function for the 5 column for example should be something like “sum(if Quant – Special Stock Num Count = 5, then 1)”. But how can I make sure each individual bin only subtracts its individual Special Stock count? I do not want it to be total number of bins with X quants, (maybe 250 bins) minus a total of 60 Special Stocks for that Storage Type = 190. I need to be counting bins with a 0, 1, 2, 3, … count of Quant – Special Stock.

To be clear, there are a handful of Storage Types, which each have thousands of Bins, all with a unique name like "HW234" etc. A "Quant" indicates a unique material number stored within the bin. So a bin with 5 quants stores 5 unique materials. We want to know how many bins have too many (non special stock) materials in them.

So I need to know what expression should be used in the expression columns, which will return a value counting Bins which have the specified count of Quants minus Special Stock Num Count (both fields are by bin number).

Thank you in advance for your help!

2 Replies

Re: How can I count bins with a certain count of [field] minus [field]?

please post some sample data and your expected result

New Contributor II

Re: How can I count bins with a certain count of [field] minus [field]?


Output chart will look something like this. In fact, this is what it already is like, but it does not subtract out special stocks. The expression used in the 4 column, for example, is:

=sum(if(aggr(aggr(sum(Quants), Storage_Bin,Storage_Type, datestamp),Storage_Bin,Storage_Type, datestamp)=4,1,0))

Datestamp is used because there are multiple days of data brought in for use in other objects.

Here is what the data looks like:

Storage Type                Bin               Quants               Special Stock Quant

10                                AA120               4                                   2                                  

40                                BG560               6                                   3                                  

10                                AA256               3                                   3                                      

10                                BG554               1                                   0                                       

50                                HT229               1                                   1                                       

50                                AA589               2                                   0                                       

40                                AA765               5                                   2                                  

40                                CF345               8                                   4                                       

10                                HG876               4                                   0                                       

40                                HG494               4                                   4                                         

50                                CF234               5                                   3                                 

So how many bins have a Quant - Special Stock Quant =

0?  Answer: 3

1?  Answer: 1

2?  Answer: 3

3?  Answer: 2

4?  Answer: 2

5?  Answer: 0

These numbers would be the totals at the top, because they are not discriminated by storage type. The rows of the chart will be.

Thank you for your help!