7 Replies Latest reply on Jan 24, 2019 12:28 PM by swaroop.gantela

    Homemade Forecasting

    Anne StClair

      Hi everyone,

       

      I was tasked to build a forecasting tool which takes into account a variety of things and I've hit a bigger bump in the road and was hoping someone can help me figure this out.

      The logic is the following:

      I have a live database from which Tableau is pulling in historical data (updated regularly) and I'd like to "blend" this with with data that is not in said database. I've built an Inventory example to help try to paint the picture (see attached excel file). I want to query the DB with the historical data as of yesterday (not extracting it to Excel, because it defeats the purpose of being dynamic), and then 'merge' it with my forecast. The forecast takes the historical data and multiplies it by a growth factor (a parameter I've created) and also takes into account the inventory that will be coming in next month. So, here my two questions:

       

      1#) Suppose it's January and I want the calculated field for my Inventory Forecast to calculate for February =>

      Total Inventory (in January) + Inventory_forcasted to be shipping (in February) + Inventory_Forecasted to be in warehouse (in February)

       

      #2) For the very same purpose, I've created a "Date" field that is compatible with the date field from my live DB, well, theoretically. When I try to query data from the DB it cuts me off as of yesterday and won't continue with the date field from the Excel (meaning: if, for example, I pull in "Inventory_forcasted to be shipping", it will stay blank because the date field doesn't extend). Any ideas as to how to fix this?

       

      Thanks so much for your help!

      --Anne

        • 1. Re: Homemade Forecasting
          swaroop.gantela

          Anne,

           

          Apologies, I may not have caught the gist,

          but it would be my impression that you may want to join your sources rather than blending them.

           

          I made up one line of historical data and used the join criteria shown below.

          Namely, I added one month to the historical date, and joined that to the forecast date.

           

          Please see xlsx and twbx v10.3 attached in the Forum Thread:

          Homemade Forecasting

           

          291297forecast.png

          • 2. Re: Homemade Forecasting
            Anne StClair

            Hi Swaroop

             

            thank you for your message. I'm currently working on joining the data tables, but since they're live and massive it's a time-consuming task.

             

            Do you or someone else maybe have an answer to my calculation problem:

            i.e. February Total Inventory = “Total Inventory” (in January) + “Inventory_forcasted to be shipping” (in February) + “Inventory_Forecasted to be in warehouse” (in February)

             

            • 3. Re: Homemade Forecasting
              swaroop.gantela

              Anne,

               

              Hmm. The joining of massive data may be problematic.

              Their might be way to limit the join ...

               

              I'm not completely sure how much that problem will be alleviated by blending,

              but I tried it anyway.

               

              Attached is a workbook with two sources blended.

              It uses the same general scheme: adding one month to the historical month,

              and using that as a connector to the forecast month.

               

              It would be my impression (but I could be wrong) that in order to add together

              the three parts you desire, their values need to eventually end up all in the same row

              in the composite datasource. That is want is attempting to be accomplished

              by the join or the blend. Once that is done, then calculation should just be an

              add (in the below case, it explicitly shows fields coming from the blend):

              SUM([Inventory All])

              +

              SUM([forecast (291297join)].[Inventory forcasted to be shipping])

              +

              SUM([forecast (291297join)].[Inventory  Forecasted to be in warehouse])

               

               

              But it is also possible that I have missed the boat entirely.

               

               

              Please see workbook v10.3 in the Forum Thread:

              Homemade Forecasting

              1 of 1 people found this helpful
              • 4. Re: Homemade Forecasting
                Anne StClair

                Swaroop,

                 

                No, you haven't missed the boat at all I see what you're going for and I think this will work for the case I outlined. Thank you!

                 

                Maybe you also have an idea for a case that builds on top of that one. I've been trying to build a calculated field that takes the last piece of historical information, which sits in said live database from which Tableau is pulling historical data - let's call it "Inventory (historic)". In an Excel Sheet I have assumptions on what might be in the warehouse and what will be on its way there ("Inventory_Forecasted to be in warehouse" and "Inventory_Forcasted to be shipping" respectively) - to stick with the previous example.

                I'd like Tableau to pull the last month of what was actually the inventory and then add the assumptions from the Excel sheet - the calculation is basically the same as above (current Inventory + "Inventory_Forecasted to be in warehouse" + "Inventory_Forcasted to be shipping" , but Tableau won't produce an output for the forecasting period. For example, my last data set of historical data is December 2018, so Tableau will calculate January 2019 with last month's actual's plus my assumptions, but won't pull the calculation all the way to, here,  June 2020.

                 

                The reason why I'm not simply adding them up, is because for looking at the remaining year 2019 and 2020, I need it to always look at the previous month and then add the assumptions for the future months.

                I.e.

                Jan 2019 "Inventory Prediction" (category "A") = 153,526 ("Inventory (historic)") + 137,514 (forecasted to be in warehouse, Jan 2019) +21,386 (forecasted to be shipping, Jan 2019) = 312,426

                February 2019 "Inventory Prediction" (category "A") = 312,426 (January 2019 "Inventory Prediction") + 137,281 (forecasted to be in warehouse, Feb 2019)+ 18.958 (forecasted to be shipping, Feb 2019)= 468,665 (which Tableau is currently not calculating).

                 

                I've created a quick sample workbook to showcase my problem.

                 

                Truly appreciate your input!

                --Anne

                • 5. Re: Homemade Forecasting
                  swaroop.gantela

                  Anne,

                   

                  Thanks for the workbook and detailed expected value.

                   

                  I made a further attempt, not sure how well this can be adopted to your true setup.

                   

                  My datasource has historic on one sheet, forecast on the other.

                  Sheets joined as described before. This time, there is only one instance

                  were the incremented HistoricDate = Forecast Date, so hopefully

                  it will cut down on the join time?

                   

                  First I needed to unify the categories from the two sheets:

                  IF NOT(ISNULL([Category])) THEN [Category]

                  ELSE [Category (forecast)]

                  END

                   

                  I think the key to the next part is to employ the PREVIOUS_VALUE() function.

                   

                  IF ISNULL(ATTR([Category (forecast)])) THEN 0    //historic, not forecast so 0

                  ELSEIF ATTR([Date Hist])=WINDOW_MAX(MAX([Date Hist])) THEN  //pull in the last historic
                      SUM([Inventory (historic)])
                      +SUM([Inventory  Forecasted to be in warehouse])
                      +SUM([Inventory Forcasted to be shipping])
                  ELSE
                  PREVIOUS_VALUE (0)  //the zero only applies to first row, not really used here
                  +SUM(ZN([Inventory  Forecasted to be in warehouse]))
                  +SUM(ZN([Inventory Forcasted to be shipping]))
                  END

                   

                  It seems to give the first two forecast amounts.

                   

                  Please see workbook v10.3 and datasource attached in the Forum Thread:

                  Homemade Forecasting

                   

                  291297forecast3.png

                  1 of 1 people found this helpful
                  • 6. Re: Homemade Forecasting
                    Anne StClair

                    Hi Swaroop,

                     

                    sorry for the delayed reply.

                    I just looked your workbook and at what you did there and it's actually EXACTLY what I need. This is so great. Thank you so much for your time and effort to come up with this suggestion!

                     

                    Best,

                    --Anne

                    • 7. Re: Homemade Forecasting
                      swaroop.gantela

                      Anne,

                       

                      Glad that it was helpful.

                      All the best.