Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please see below.
Below I have inventory data .Doc Type IC means its Inventory Completions which means its in stock. BI means its Invoiced, meaning it has been sold.
I would like to get the Lot Numbers which are in stock (IC) but have no BI.
So this example below means it’s been sold already hence the Negative value for Doctype BI. I want to create an expression which counts the Lot Numbers which are IC but have no BI. So I want to, in essence pull the latest record per Lot Number.
In the example below the max record would be the record of the 2018/05/02.
Location | CalDate | ItemCode | LotNumber | DocType | Qty |
FG2 | 2018/04/01 | 011P021250800 | 17N0001/1A | IC | 0.696 |
FG2 | 2018/04/01 | 011P021250800 | 17N0001/1A | IB | 0 |
FG2 | 2018/05/02 | 011P021250800 | 17N0001/1A | BI | -0.696 |
Hope this makes sense ?
Maybe this
Count(distinct{<DocType={'IC'}>*<DocType -={'BI'}>} ItemCode)
Hi,
Thank you for your assistance, the above response was closer to what I was hoping to achieve. I added an exclusion on the Set expression as shown below.
Count({<DocType ={'IC'},LotNumber *= E({<DocType = {'BI','BH'}>} LotNumber) >} LotNumber)