4 Replies Latest reply on Aug 13, 2016 6:50 AM by Jonathan Drummey

    Forward Weeks supply calculation

    Philip George

      Hi,

       

      I am trying to calculate the forward weeks supply based on each weeks inventory.Result expected is shown in Green in the below screenshot.

       

                                                                                                                                                           

      Wk0 I have an inventory of 400 and I can satisfy the demand for 3 weeks(demand of Wk0+Wk1+Wk2). Hence the result is 3.

      Wk1 I have an inventory of 500 and I can satisfy the demand for 3 weeks(demand of Wk1+Wk2+Wk3). Hence the result is 3.

      Wk2 I have an inventory of 300 and I can satisfy the demand for 2 weeks(demand of Wk2+Wk3). Hence the result is 2.

       

      Can Someone help me to get this calculation in Tableau. Any help is appreciated.

       

      I have attached a Tableau workbook also.

        • 1. Re: Forward Weeks supply calculation
          Benjamin Greene

          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])

          THEN 0

          ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 1)

          THEN 1

          ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 2)

          THEN 2

          ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 3)

          THEN 3

          ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 4)

          THEN 4

          ELSEIF SUM([Inventory on hand])<WINDOW_SUM(SUM([Demand]), 0, 5)

          THEN 5

          ELSE LAST()+1

          END

           

          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.

          2 of 2 people found this helpful
          • 2. Re: Forward Weeks supply calculation
            Philip George

            Thanks Benjamine.

             

            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?

             

            Regards,

            Philip

            • 3. Re: Forward Weeks supply calculation
              Philip George

              Jonathan Drummey Shawn WallworkJoe Mako

               

              Hi All,

               

              Could you also please have a look into the calculation.

               

              Regards,

              Philip

              • 4. Re: Forward Weeks supply calculation
                Jonathan Drummey

                Hi Philip,

                 

                @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.

                 

                Jonathan