4 Replies Latest reply on Oct 6, 2016 3:25 PM by Tim Wilson

    Calculation and Display of Rolling Forecast

    Tim Wilson

      Hi all,


      I'm trying to create a dashboard to show a rolling forecast of inventory, using part leadtime and forecast data as inputs:

      2016-10-03 14_30_30-Inventory_Planner_Example.xlsx - Excel.png2016-10-03 14_30_35-Inventory_Planner_Example.xlsx - Excel.png

      I've created this in Excel by creating an intermediate table (see below) that tabulates demand vs required purchase date using combination of SUMIFS and VLOOKUP, but would really like to build it into Tableau to allow more advanced analysis and interrogation.  Can I build a similar intermediate calculated field / table in Tableau somehow?


      2016-10-03 14_30_40-Inventory_Planner_Example.xlsx - Excel.png

      Can anybody shed any light on how I could do this?  This is what I'm trying to end up with, but in Tableau:


      2016-10-03 14_30_43-Inventory_Planner_Example.xlsx - Excel.png


      Sample Excel file attached.


      Many thanks in advance!



        • 1. Re: Calculation and Display of Rolling Forecast
          Łukasz Majewski



          Since you have your data in 2 tables it is rather complicated to replicate excel's vlookup results. Anyway this is based on cross joined data:

          I would suggest reshaping data first.

          1 of 1 people found this helpful
          • 2. Re: Calculation and Display of Rolling Forecast
            Tim Wilson

            Hi Łukasz,


            Many thanks for your help.  The end result looks great, but I'm struggling a little to understand the logic behind how you achieved this.


            You added a blank column named 'x' to data ranges FC and FD, and then joined based on this column.  Does this simply allow the data to be joined despite not having any common fields?


            Range FC is an aggregate of data from range D.  I constructed this in Excel as I didn't know any other way to get my end result, but it's aggregate data, not raw data.  In my real data set I have other data that would be useful to analyse such as suppliers, stock levels etc that gets lost in the aggregation step.  Can I get straight to the end result without using the intermediate table FC in Excel, or does this need to be generated outside of Tableau?


            Lastly, I'm struggling to wrap my head around the table calc 'Calculation 1' that you created.    I don't quite get the indexing logic that you built.  Is there any way to provide an overview of the logic here?


            Thanks so much for your help, and sorry for all the additional questions!



            • 3. Re: Calculation and Display of Rolling Forecast
              Łukasz Majewski

              Since tableau does not allow cross joins to be made in its join interface I used that dummy field 'x' (in fact I was surprised it worked with blanks/NULLS as you cannot compare those in SQL so some value should be used instead).


              You may get straight results by creating a cross joined table in excel - is excel your actual db for this?


              Indexing logic is complicated....

              I think your goal is to have a diagonal matrix with month on X & Y so the real data lies only on that diagonal line (1x1, 2x2, ...) whereas you want to display a multiplied values based on relative position against X & Y so I had to trigger 'data densification' (or 'domain completion' - I never know what's the right name) and lookup the values from the diagonal line.

              1 of 1 people found this helpful
              • 4. Re: Calculation and Display of Rolling Forecast
                Tim Wilson

                Hi Łukasz,


                Thanks for the detailed explanation. My real data is in excel, so I'll try the cross-join method that you mention above.  It may be an option for me to restructure the original data, but that will take some time so I need to carefully consider options. 


                On the indexing logic, I think I'll need to spend some time experimenting to try and wrap my head around it fully!


                Thanks again,