topic Re: Inventory on Date in App Development

Inventory on Date

CHL — Fri, 18 Feb 2022 14:17:27 GMT

Hi All,

I have a system which has stock moving in and out small example below but actual data goes back 5 years:

ID	Time In	Time Out
1	01/01/2022 00:33	15/02/2022 14:00
2	01/01/2022 00:33
3	01/02/2022 15:47	02/02/2022
4

What I need to show, is what the inventory was on a certain day. That being items which arrived before that date, but had not left at that time.

I have tried multiple ways to hardcode values such as: SUM({1<[Time In] = {"<2021-10-01 00:00:00.000000"}, [Time Out] = {">2021-10-01 00:00:00.000000"}>} [Item Count] )

Only my math has deserted me and I cant think of way to get this working. Do I need to do a Peek in my load script?

Any help is appreciated!

Re: Inventory on Date

Vegar — Sat, 19 Feb 2022 19:34:27 GMT

You can try to solve this by using intervalmatch()

Take a look at this sample script. I hope it can help you into the right direction.

Data:
LOAD
ID,
[Time In],
[Time Out],
DayName([Time In]) as [Date In],
DayName([Time Out]) as [Date Out]
inline [
ID, Time In, Time Out
1, 01/01/2022 00:33, 15/02/2022 14:00
2, 01/01/2022 00:33,
3, 01/02/2022 15:47, 02/02/2022
4, ,
];

for _date=yearstart(today()) to '15/02/2022'
Mastercalendar:
LOAD
WeekName('$(_date)') as YearWeek,
MonthName('$(_date)') as [Yearmonth],
dayname('$(_date)') as [Date],
floor(NormInv(rand(), 1200, 200)) as [Total outstanding]
AutoGenerate 1;
next

intervalmatch (Date)
Load [Date In],[Date Out]
Resident Data
;

Re: Inventory on Date

PrashantSangle — Sun, 20 Feb 2022 06:16:23 GMT

it is difficult to guess what is exact error. might be date format can also be issue. Better if you provide some sample data with expected output.

Regards,

Prashant Sangle