I have a problem with calculating a field. Please help
Here is the situation.
We have several 'Products' in a table. For each product there are orders in table 'Orders' that have quantity and date. Each product also have a have 'In stock quantity'.
If the orders are more than the stock quantity then in a moment of time the orders can't be fullfilled because we are out of stock. I need to have a table that shows
'Product - Date when we are out of stock'.
I have succesfully done it in the load script calculating the in stock quantity after each order and I have this date.
The problem is that now I want to have input fields where I can input a change in the order quantity. When the order quantities change the Date when we are out of stock should be recalclulated. That's why I can't use the one that I have found in the load script and I have to do it in a table.
I think I need a table that have Product as dimension and expression should be something like:
I try to aggregate by date and if the quantity for the current and all the previous dates is more than the stock quantity to return the date. In the end get the first date.
Unfortunately it doesn't work. I tried some other ideas with rangesum etc because I think the problem might be in the set analysis but none worked. Please help because I am almost out of ideas.
I believe it should be possible to make such table if I have the data.
--------------------
Here I added a sample file. I have two tables. The top one shows products. The bottom one shows orders and have input field.
I want the top one to show me the date when the product will go out of stock and when I make changes in the order below this date should change also.
In the example for Product 1 it should show 1.1.2011 and for Product 2 it is 1.2.2011
