Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I think an As-Of Table in your data model should work well for you:
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.
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