6 Replies Latest reply on Nov 20, 2014 2:09 AM by Simon Runc

    How to compare forecasted sales vs goals?

    marcos.ayala

      Hi,

       

      I'm trying to create a view where I could compare the forecasted sales calculated by Tableau against the projected/calculated goals. The datasource contains 24 months of data ending in Sep'2014. Using Tableau forecast option I was able to calculate the sales of the following 9 months (Oct'2014-Jun'2015). By creating a calculated field I developed the Goals (ex. Apr'2014 - Sep'2014 Sales * (1 + Goal Forecast). Now, how can I spread the Goal Forecast sales under S1 2015 months? I need to create a view similar to the one below

       

      sales_sample.png

        • 1. Re: How to compare forecasted sales vs goals?
          Simon Runc

          hi Marcos,

           

          Here you go. I've just selected 'Show Trend' rather than Trend & Forecast for the Trend Show Option. I've also used the measure names on shelf to plot in one chart. Hope this is what you were after.

          • 2. Re: How to compare forecasted sales vs goals?
            marcos.ayala

            Thanks! that was quick and helpful.

            • 3. Re: How to compare forecasted sales vs goals?
              marcos.ayala

              Simon,

               

              What you did was great but I notice that if I change the baseline parameters to a far back date the forecast ‘Trend’ doesn’t work. For example, if I use as a baseline April 2013 through September 2013 the graph shows negative values. See below screenshot:

               

               

               

              Thanks,

               

              Marcos

              • 4. Re: How to compare forecasted sales vs goals?
                Simon Runc

                hi Marcos, Yes I see your problem, and am not sure there is a simple solution. As you've probably worked out if you don't have the through date as the last week of sales, then it see's any weeks between your through to date, and the last date as zero so takes this into account on the forecast!...such as below;

                 

                Forecast Problem.JPG

                 

                If you try and set the non-required weeks to Null instead of Zero, the forecast option won't work. The other way round this (which I have voted for in the ideas section!...although it's not got many votes yet!) is to use a table calc, so that you created the Base as the Average of the Weeks to be looked at, so any forecast would just continue that, flat, trend. However Table Calcs (like Nulls) aren't supported by forecast. I've had (albeit a quick) look round the forum/blogs and can't find anyone who has managed to solve this, without creating the forecasts in the data source (so circumventing tableau forecast altogether).

                 

                All I can suggest atm, is that you hide the Baseline End part from the end user (as long as the baseline end is the last week of sales, it works). For seeing the baseline over a specified period (as a flat average) you could create a second viz, which on selection of a To and From would show the Average Line, but not as a forecast (this would be the table calc - let me know if you want me to send you over a version of this if you haven't used table calcs that much).

                 

                ...in the meantime I'll keep thinking, to see if a light-bulb goes off!

                 

                It might also be worth re-posting as a new question (with the problem re-stated per your image), so some of the 'great-and-good' can cast their eye over it, and may well have a solution I've not thought of (as an answered question this probably won't get that much air-time!)

                • 5. Re: How to compare forecasted sales vs goals?
                  marcos.ayala

                  Thanks! Can you please send me a version of the flat avg baseline?

                  • 6. Re: How to compare forecasted sales vs goals?
                    Simon Runc

                    hi Marcos,

                     

                    This is the best I've come up with! I had an idea last night (...such is the Rock'n'Roll life I lead!!) of using the Reference Line (based off a table calc), but again the second you have any table calcs in the view none of the forecasts work! Really hope they sort this for T9!

                     

                    On the attached I've created a new BASELINE called 'GOAL_BASELINE_NULL'

                     

                    IIF([Period]>=[BASELINE START] AND [Period]<=[BASELINE END],[Sales],NULL)

                     

                    This is just the same as your formula but equates to NULL, rather than Zero if out of date range. This is so the Table Calc ignores the dates outside the requested range (if they are Zero it takes this into account when creating the average)

                     

                    I've then created the Table Calc, Called 'GOAL_BASELINE - Table Calc' with the formula

                     

                    WINDOW_AVG(SUM([GOAL_BASELINE_NULLS]))

                     

                    I've brought these into the view, running the Table Calc Table (Across) [i.e. by Period].

                     

                    I've then put both in a Dashboard (so you get both together, and made the Axis (manually) line up to make it clear what refers to what.

                     

                    Now as you change the date range for the 2 parameters, the Orange line will be an average of the selected period (btw the Blue part of the line highlights which period is selected for the base)

                     

                    Not perfect, but without lots of complicated pre-processing/re-shaping (adding Padding Months...etc.), this is best I can come up with.

                     

                    Hope it helps.