topic Re: Inventory on date in App Development

Inventory on date

CHL — Fri, 17 Feb 2023 15:54:38 GMT

Hi,

I have a table with inventory movements like below:

Stock ID	Time In	Time Out	Movement Count
12345	01/01/2023 04:20	01/02/2023 17:00	1
12643	02/01/2023 18:00	02/01/2023 21:00	1
57543	01/01/2023 15:00	03/01/2023 14:21	1

I have a master calendar setup.

I need to report a count of 'stock' at midnight every day, that being stock that entered before midnight and left after midnight.

So based on the example above:

Date	Count
01/01/2023	2
02/01/2023	1
03/01/2023	0

I attempted to chart this using my Calendar date as the dimension and this measure:

SUM({<Date = {"<=$(=DATE(MIN([Time In])))>=$(=DATE(MAX([Time Out])-1))"}>} [Movement Count])

and although returning values they are not correct.

Is there a way?!

Thanks

Re: Inventory on date

vinieme12 — Mon, 20 Feb 2023 03:46:04 GMT

Your dataset needs transformation to be able to evaluate this

refer below

temp: Load * ,date(StockDate+iterno()-1) as StockDate2 While StockDate+iterno()-1 <= (StockDate+if(StockDays=0,1,StockDays)-1) ; load StockID ,TimeIn ,TimeOut ,Date(floor(Timestamp#(TimeIn,'DD/MM/YYYY hh:mm'))) as StockDate ,MovementCount ,floor(Timestamp#(TimeOut,'DD/MM/YYYY hh:mm'))-Floor(timestamp#(TimeIn,'DD/MM/YYYY hh:mm')) as StockDays inline [ StockID,TimeIn,TimeOut,MovementCount 12345,01/01/2023 04:20,02/01/2023 17:00,1 12643,02/01/2023 18:00,02/01/2023 21:00,1 57543,01/01/2023 15:00,03/01/2023 14:21,1 ];

in chart use below expression

Count({<StockDays={">0"}>}Distinct StockID)