# 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).

2 Replies
MVP

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

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 =