I have two values that I'd like to subtract from each other and display in a graph as well as a table.
The first value, is my inventory, which easily can be summed up as Sum (Inventory) ---> This value is only available for this day. So our Inventory yesterday is no longer available. If I filtered on a date other than today, this week, this month or this year, It would turn up as 0. Inventory will show for the year/month/week because today's date is included in those time periods.
I then have an estimated sales, which can be summed up as Sum (Order_Budget_Qty). This will show how many units we estimate to sell on any given date. It can be summed up to any time period, historically or future date. Although, only future estimates are of value since historically, are our actual sales.
Problem:
I want to display a weekly line graph, where the leftmost value is our current stock and then subtract an accumulated value of Order_Budget_Qty each week showing how our inventory is affected by our estimated sales.
Something like this:
| Date | w1 | w2 | w3 | w4 | w5 |
| Inventory | 10,000 | 0 | 0 | 0 | 0 |
| Ordered_Budget_Qty | 500 | 1000 | 1000 | 900 | 800 |
| Result | 9,500 | 8,500 | 7,500 | 6,600 | 5,800 |
Then, as we move into week 2, say we sold 833 units in week 1, it would mean that the inventory is 9,167 and this would be the new value, which would be subtracted from.
Inventory is already subtracted by actual sales daily, so the expression does not need to account for this, only the fact that I want to show it all in a graph and display the decreasing inventory over time by subtracting the estimated sales.
