This is a tough one for me. I'm sorry I can't come up with a more generalizable/scalable solution, but here's what I've got. I made a How Many Weeks Can Be Supported calculated field with the following syntax:
IF SUM([Inventory on hand])<SUM([Demand])
ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 1)
ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 2)
ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 3)
ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 4)
ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 5)
This basically compares the current Inventory on hand value to the sum of the upcoming demand values, going out five weeks. When you replicate this in a real data set, you'll want to add as many more of those ELSEIF, THEN statements as it takes to account for the greatest value in the data. Finally, the ELSE LAST()+1 part of the calculation is in there just to give the correct values for the last couple rows in the data, where the Inventory on hand is greater than the sum of all the upcoming Demand values.
Demand and Supply.twbx 33.8 KB
I also got this long calculation for future 26 weeks. But it works.Thanks again.
Is there any way we can make the offset dynamic so that we can avoid the long calculation?
@Greene's calculation is how I'd do it, here's an explanation why:
Computing forward weeks supply is a type of fitting calculation where for each week the inventory for the current week needs to be compared to 0 (current) to N future weeks and the best fit is the Nth week that has a running sum of demand that is less than the 0th week's inventory. The usual way this is done in languages that don't have a higher-level fitting function defined is by iterating over the 0,1,2,3,...N weeks until the best fit is determined. Tableau does not have the ability in its calculation language to iterate multiple times for a single mark***. @Greene's calculation works around this by using the long IF statement so all the iterations are done as separate computations inside a single calculation. You could write a simpler-looking calculation using Tableau's R integration in a for/next or while loop, but I don't know that it would be any faster than the nested calculation due to the overhead of calling R.
***Tableau has only one self-iterating function. Using PREVIOUS_VALUE() we can iterate a calculation over multiple marks, but not have multiple iterations in a single mark.