3 Replies Latest reply on Feb 10, 2019 6:39 PM by Chris McClellan

    Actual & Forecast prepping data

    emily nguyen

      Hi All,

       

      Would anyone have ideas on how I could easily manipulate by data to make it Tableau friendly in the data source. At current I can only pivot on either Actual or Forecast not both.

       

      Below is a snippet of how my excel is looking ( actual file extends out for the full year)

       

      ProjectBudgetJan ActualsFeb ActualsJan ForecastFeb Forecast
      11233642
      2134322757
      3664425352
      42347722
      56568955

       

       

       

      Thanks in advance

        • 1. Re: Actual & Forecast prepping data
          Chris McClellan

          Depending on your version of Desktop and your data source (this will work in Excel) you can do this ....

           

          - connect to the Excel spreadsheet

          - in the data preview pane, select the Actuals columns (Jan thru Dec), then right click and choose "Pivot"

          - then select your Forecast columns and choose "Add Data to Pivot"

          - you now have 2 columns - Pivot Field Values (which you might want to rename to Values) and Pivot Field Names

          - create a calculated field called Month and use this formula : left ([Pivot Field Names],find([Pivot Field Names],' '))

          - create a calculated field called Scenario and use this formula : mid ([Pivot Field Names],find([Pivot Field Names],' '))

          - you can also hide Pivot Field Names

           

          The data should be in the right format and easier to use in Desktop.

          • 2. Re: Actual & Forecast prepping data
            emily nguyen

            Hi Chris,

             

            Thanks for the tip! I don't think I explained my question properly.. At the moment if I pivot all my months actuals and forecast, the actual and forecast numbers are all put into one column with the other pivot column being the month. With that I'm not able to differentiate which is actuals and forecast. This is making it tricky for me to do Actuals and Forecast comparisons.

             

            Would you have any solutions?

             

            Thanks!

            • 3. Re: Actual & Forecast prepping data
              Chris McClellan

              If you do the 2-step pivot (ie Add to Pivot rather than pivotting everything at the same time), then it should work for you - it did for me

               

               

              BEFORE

              2019-02-11 13_38_22-Tableau - Book9.png

               

               

               

              AFTER (scrolled down a bit to show Actual & Forecast values in the same column)

              2019-02-11 13_38_54-Tableau - Book9.png