I have a request in to somehow analyze and perhaps visualize how many inventories were/are available per day. This might be more simple than I am envisioning, so I hope that’s true.
Our inventories have a bit of complexity to them as well. For instance, we only consider the inventory to be “available” between a range of dates that are associated with the particular unit.
Consider the following table of information. This shows 2 distinct parts (ABC and DEF) each with 2 lots, for a total of 4 distinct records. Each record has a date range associated with their availability.
Part
Lot
Release
Expires
ABC
123
1/1/2021
1/5/2021
ABC
124
1/1/2021
1/7/2021
DEF
125
1/5/2021
1/10/2021
DEF
126
1/5/2021
1/7/2021
If I extend this table to show by day with a “1” indicating that the unique record is available that day, the first ten days of the month would look like this:
Part
Lot
Release
Expires
Day
1
2
3
4
5
6
7
8
9
10
ABC
123
1/1/2021
1/5/2021
1
1
1
1
1
ABC
124
1/1/2021
1/7/2021
1
1
1
1
1
1
1
DEF
125
1/5/2021
1/10/2021
1
1
1
1
1
DEF
126
1/5/2021
1/7/2021
1
1
1
1
Total Available
2
2
2
2
3
3
3
2
1
1
Total of Part ABC
2
2
2
2
2
1
1
0
0
0
Total of Part DEF
0
0
0
0
1
2
2
2
1
1
From the table, I now know the total number of unique items that were available per day as well as by part which items were available by day.
This is what I am looking to do with some SQL or BAQ code.
I am not really sure where to start, so any guidance is much appreciated!
Are your users to look at it in a dynamic amount of ways? or just a few defined ways like you have illustrated above? Sounds like you are looking for an Executive Dashboard or a report in a BI tool. You could probably achieve the result in a regular dashboard if you had a bunch of grids.
We tie together PartDtl with filters on SourceFile (i.e., Indicates the record type that created this record.
JH-JobHead, JA-JobAsmbl, JM-JobMtl, JO-JobOper, PO-PoRels, OR-OrderRels, FC-ForCast ) along with PartBin and take it out to Excel, about weekly, to review where we will have shortages. I think you should be able to use same query to get at your data/report.
Have you looked into using PartDtl for summing demand and supply, along with Partbin for summing QOH, to get at the data?
Hey Chance, I’m just looking to get the output of “N number of X Part” per day in the final rows there as an analysis tool, doesn’t need to have a lot of drill down. Perhaps that’s the right way though, export the data and manipulate in Power BI?
Hey Nancy,
I admire the complexity of your solution. This is actually UD table data so I bet my solution will actually be somewhat less complex, but seeing your document is very similar to the end result I’m going for
Looks like you’ll need two grids for your results. First containing a query with the partlot table and a bunch of calculated fields for how many days you could need visibility out for (Im assuming you wont need something crazy beyond 30 otherwise this isn’t realistic). Second grid would just be an identical query that is wrapped with a parent subquery to group by Company and Part. The parent subquery will help you easily count across lots.
