6 Replies Latest reply on Apr 10, 2013 5:57 PM by John Rodat

    What are the data requirements for trend lines and forecasts?

    Lauren MacMillan

      Hi,

       

      I'm still relatively new with Tableau so this is probably a simple question, but the forecast feature in Tableau 8.0 doesn't seem to be available for the data I am working with. (Forecast is grayed out when I right click the chart). It's currently a pretty simple chart- Sum(Expenditures) on Rows and year on Columns. I have 13 data points (years 1999-2011). I also tried to display a trend line, but I get a warning: Capture_warning.PNG

       

      I've turned off Stack Measures, as I read a post about how Trends don't work with that, but am trying to figure out what the underlying issue is. I think it may be that there aren't enough data points to calculate a trend or forecast, but wanted to see if anyone knew what the requirements were.

       

      Thanks!

      Lauren

        • 1. Re: What are the data requirements for trend lines and forecasts?
          John Rodat

          Lauren

           

          I and others have had the same problem. Regrettably, for the forecast function, Tableau requires a date (day, month, year), rather than just a year regardless of the field format.

           

          Depending on your data, you may be able to do a work-around. For example, try creating a new date field, using the year field in your original data and another field representing the first or last day of the year, whichever is more appropriate for your purpose.

           

          Hope this helps.

          1 of 1 people found this helpful
          • 2. Re: What are the data requirements for trend lines and forecasts?
            Lauren MacMillan

            Hi John,

             

            Thanks for the response! I just tried re-formatting the year to be 01/01/1999, 01/01/2000, etc., and the Forecast option still seems to be grayed out and I get the same warning when I try to create a Trend Line. Do you know if there is also a minimum number of data points Tableau needs to be able to run these calculations? I think I read somewhere it was 24 for forecasting at the year level, but wanted to see if that was my issue.

             

            Thanks again,

            Lauren

            • 3. Re: What are the data requirements for trend lines and forecasts?
              Tracy Rodgers

              Hi Lauren,

               

              Can you post a screenshot or workbook (twbx file)?

               

              -Tracy

              • 4. Re: What are the data requirements for trend lines and forecasts?
                Lauren MacMillan

                Hi Tracy,

                 

                Here's a screenshot of the sheet with just SUM(Spend) on rows and year on columns. You can see here that "Forecast" is grayed out. I'm guessing this is because I don't have enough data points, but just wanted to confirm what the requirements are.

                 

                Thanks!

                Lauren

                Screenshot-Forecast.png

                • 5. Re: What are the data requirements for trend lines and forecasts?
                  John Rodat

                  Lauren

                   

                  When in doubt, experiment ...

                   

                  See the attached Excel file, "Projection Test File". In the date column, you'll see a little formula that converts "year" into a usable date. You'll also see a series of measures that are labeled based on the number of data points each has. The remaining cells are null.

                   

                  Then see the attached twbx file, "Projection Test." You'll see that most don't generate a projection, but those with the most data (Eleven Data Points and Twelve Data Points) do. So far, I've gone no further.

                   

                  That's the good news. The bad news is that "Eleven" and "Twelve" appear to be identical both in the actual and the estimate. I'll continue playing with that to try to figure it out, but in the meantime, this may be helpful. Or you or someone else may be able to spot what I've done wrong with Eleven and Twelve.

                  • 6. Re: What are the data requirements for trend lines and forecasts?
                    John Rodat

                    Lauren

                     

                    OK, I think I've got it. See attached file, "Projection Test Workbook 2."

                     

                    Scroll down to the bottom of Sheet 1 in the .twbx file. You'll see that the forecast function is working for the last measure, "Twelve Data Points," but not for the others. The estimate is re-colored from the earlier file to highlight it.

                     

                    In any case, it is completing the projection. It's currently set to project 1 period (year), but can be extended with the Forecast Options.

                     

                    Two key points:

                     

                    1. In this file I reset the Forecast Options so that it did not ignore the last period. That's under Source Data in Forecast Options. From an analytical perspective, ignoring the most recent period is a reasonable choice in many cases, but in this case, it was hiding the differences in the test, especially when considering the next point.

                     

                    2. The forecast function does not work with null values. So for all the measures in this little experiment, except "Twelve Data Points," the forecast function will not work. Trend lines do.

                     

                    I hope this is helpful. Going through this helped me so thanks for the question.