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

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

Anonymous — Thu, 29 Mar 2018 22:28:28 GMT

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!

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

MarcoWedel — Thu, 29 Mar 2018 23:36:28 GMT

please post some sample data and your expected result

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

Anonymous — Fri, 30 Mar 2018 18:10:03 GMT

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!