Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
MarcoWedel

please post some sample data and your expected result

Anonymous
Not applicable
Author

Capture.JPG

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!