Inventory on Date

[CHL]

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!

[Vegar]

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
;

[PrashantSangle]

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