topic Re: Warehouse in specific date in App Development

Warehouse in specific date

Anonymous — Mon, 13 Aug 2018 17:11:12 GMT

Hi everyone,

first post in the comunity

I need to create a graph for warehouse during time. I have warehouse situation in this moment and all the Sell Out and Sell In of the past day. With MasterCalendar I have create calendar from min and max date present. Now I need to calculete the qty of warehouse in that specific date. I try with RangeSum and Above but I have the limit of row sorting and filter on row... Don't work fine.

For exemple warehouse

Item	Quantity
CODE_A	3
CODE_B	4

Movements

Sell	Item	Date	Quantity
IN	CODE_A	12/08/2018	+ 1
OUT	CODE_A	12/08/2018	- 2
OUT	CODE_A	10/08/2018	- 1
OUT	CODE_B	11/08/2018	- 1
OUT	CODE_B	10/08/2018	- 3

I need something like this:

Item	Date	Quantity
CODE_A	12/08/2018	4
CODE_A	11/08/2018	4
CODE_A	10/08/2018	5
CODE_B	12/08/2018	4
CODE_B	11/08/2018	5
CODE_B	10/08/2018	8

Re: Warehouse in specific date

petter — Mon, 13 Aug 2018 20:01:05 GMT

I think an As-Of Table in your data model should work well for you:

The As-Of Table

Re: Warehouse in specific date

jheasley — Tue, 14 Aug 2018 01:25:52 GMT

so another option would be to add a 3rd dimension to the sell table for the start date of each month. your entry for december may look like this:

Sell	item	date	quantity
Start	CODE_A	12/08/2018	3
IN	CODE_A	12/08/2018	1
out	CODE_A	12/08/2018	-2

if you have the start for each date, you can then effectively calculate the sum(quantity) for any given date. as an added side effect, you can use very simple set analysis to also determine in/out/start points for aggregated time ranges.

Re: Warehouse in specific date

Anonymous — Wed, 15 Aug 2018 08:56:25 GMT

Hi all,

I try both the solutions but I foun easyer solution.

I have create Master Calendar and joined with Warehouse an sell IN & OUT groupped by date. Ordered this table for ITEM and DATE desc so I create a calculed field like this:

if(Previous(ITEM_ID) = ITEM_ID, (Peek(QTY_PROG) + [QTY_OUT] - [QTY_IN]), (QTY_WARE+ [QTY_OUT] - [QTY_IN])) as 'QTY_PROG'

Thanks everybody