10 Replies Latest reply on Oct 21, 2013 11:58 PM by Jim Wahl

    how to extract trend line information?

    Paul Reichl

      Hi,

       

      I have some data that I have been able to fit trend lines to at a series of different locations (i.e. different trends lines at different locations).

      I would now like to be able to get the trend line information (i.e. slope, intercept, p value etc) at each location as a set of new fields so that I can then perform calculations based on the trends.

      Is this possible using just Tableau desktop?, and if so, are there any examples of how to do this?

       

      Thanks in advance,

       

      Paul.

        • 1. Re: how to extract trend line information?
          Ramon Martinez

          Hi Paul,

           

          Yes, it is possible just using Tableau.

           

          Create you view putting time measure on X axis and your independent measure on Y axis. Right click on the chart and select Trend Lines option from the floating menu.

           

          As a result the trend lined will be shown.

          Now right-click on the trend line and select the option Describe Trend Line.. as a result you will have the trend line information.

           

          You can add the dimension location on Color and you will have a trend line per location. If you right-click over any trend line you will have two interesting options: Describe Trend Line... and Describe Trend Model...

           

          Describe Trend Line will give you information about the selected trend line and Describe Trend Model will give you information about every trend line and a summary of the whole trend model.

           

          From those information you can pick the regression model and create you own calculation.

           

          I'm attaching a workbook that you can use to see these examples of hypothetical trend line models.

           

          Let me know if this helps.

           

          Best,

          Ramon

          1 of 1 people found this helpful
          • 2. Re: how to extract trend line information?
            Paul Reichl

            Thanks Ramon,

             

            This seems to work, as I can copy the describe trend model data to a spreadsheet, clean it up, and then use it.

            Do you know if there is any automatic way where I can get this information into Tableau without copying it to another spreadsheet first?. This would be ideal, as then I could exclude a point (if I had a reason to do so) and the updated trend information would then flow right through (without needing to update the spreadsheet and load it in again).

             

            This is already a great improvement and should work for most cases.

             

            Thanks,

             

            Paul.

            • 3. Re: how to extract trend line information?
              Ramon Martinez

              To my understanding, there is no way to do it automatically. What you can do is to copy the trend line equation and apply it to a calculated field.to do other type of calculation, projection, etc. this way you don't need to go back to excel. 

               

              But there is no way to get the line trend equation and parameters automatically. This has a the drawback you mentioned, the trend line parameters change when data if filter or some data points are remove or excluded.

               

              Best,

              Ramon

              • 4. Re: how to extract trend line information?
                Jim Wahl

                You should be able to do this in Tableau 8.1 with the R integration.

                 

                R will give the coefficients for

                intercept                   = summary(lm(time ~ distance))$coeff[1]

                distance                   = summary(lm(time ~ distance))$coeff[2]

                p-value (distance)   = summary(lm(time ~ distance))$coeff[8]

                 

                Since these are calculated fields in Tableau and updated when the underlying data changes, you would be able to exclude a data point and the model and coefficients will update.

                 

                Jim

                1 of 1 people found this helpful
                • 5. Re: how to extract trend line information?
                  Paul Reichl

                  Thanks Jim,

                   

                  How do I get hold of Tableau 8.1?. My maintenance still has more than 6 months to run but when I download the latest version (through the portal) and install it, the version installed is 8.0.5.

                   

                  Kind Regards,

                   

                  Paul.

                  • 6. Re: how to extract trend line information?
                    Ramon Martinez

                    Hi Paul,

                     

                    Tableau 8.1 is still in beta. It will be released soon.

                     

                    Ramon

                    • 7. Re: how to extract trend line information?
                      Paul Reichl

                      I contacted my Tableau distributor and I was able to get a copy of the 8.1 beta.

                       

                      For what it is worth (after a couple of hours reading up on R and fiddling around), I am now able to calculate the slope and intercept in Tableau 8.1 using (this is my first attempt at this, but it seems to agree with what Tableau displays)

                       

                      Intercept

                      SCRIPT_REAL("mydata <- data.frame(cbind(yy=.arg1, xx=.arg2)); fit <- lm(yy ~ xx,data=mydata); fit$coeff[[1]]",ATTR([yvalues]),ATTR([xvalues]) )

                       

                      Slope

                      SCRIPT_REAL("mydata <- data.frame(cbind(yy=.arg1, xx=.arg2)); fit <- lm(yy ~ xx,data=mydata); fit$coeff[[2]]",ATTR([yvalues]),ATTR([xvalues]) )

                       

                      These appear to be right, although their calculation is somewhat slow.

                       

                      However, my P values and R^2 values don't seem to be correct.

                      They are calculated as follows:

                       

                      P value

                      SCRIPT_REAL("mydata <- data.frame(cbind(yy=.arg1, xx=.arg2)); fit <- lm(yy ~ xx,data=mydata); anova(fit)$'Pr(>F)'[1]",ATTR([yvalues]),ATTR([xvalues]) )

                       

                      R^2 values

                      SCRIPT_REAL("mydata <- data.frame(cbind(yy=.arg1, xx=.arg2)); fit <- lm(yy ~ xx,data=mydata); summary(fit)$r.squared",ATTR([yvalues]),ATTR([xvalues]) )

                       

                      These don't seem to agree with what Tableau displays.

                       

                      I am very new to R (one day of reading), so if anyone has any suggestions as to what I need to do to correct the P and R^2 values it would be most appreciated.

                       

                      Thanks in advance,

                       

                      Paul.

                      • 8. Re: how to extract trend line information?
                        Paul Reichl

                        Sorry R2 and P seem to work correctly (my filter settings were incorrectly set).

                        The addition of R functionality is great.

                        • 9. Re: how to extract trend line information?
                          Ramon Martinez

                          Hi Paul,

                           

                          Good to know that the calculation of R-squared and p-value using R integration with Tableau 8.1 is ok.

                           

                          Best,

                          Ramon

                          • 10. Re: Re: how to extract trend line information?
                            Jim Wahl

                            Hi Paul,

                             

                            From your original Tableau worksheet, here is the trend model for time series 1:

                            distance = 1, 2, 3, 4, 5

                            time =  2.85,  6.05, 10.29, 13.68, 17.31

                            2013-10-22 09-18-02.png

                            You can get a nearly identical coeff table from R using the summary() function. (The p-value is very small, but the t-value matches.)

                            > dist_vals = c(1:5)
                            > time_vals = c(2.85,  6.05, 10.29, 13.68, 17.31)
                            >
                            > summary(lm(time_vals ~ dist_vals))
                            

                             

                            2013-10-22 09-45-13.png

                            Pulling out the slope, intercept and R^2 is just a matter of find the fields in the summary function. You can do this with the command

                            str(summary(..), which will give you the structure of the summary output. You'll see a variable "coefficients" (which you can abbreviate coeff), which will give you the coeff table above. And also r-squared, adjusted r-squared, ...

                            2013-10-22 09-44-01.png

                            R has a few confusing syntax issues. It traditionally uses the <- for assignment, but you can use = interchangeably. Second, the "." in variable names is descriptive only. For example, many people will write dist.vals instead of dist_vals.  AND, if you don't have R-Studio installed, it's probably a must have for debugging the functions before using them in Tableau. There are alternatives to R-Studio, but it's free and very well supported.

                             

                            Jim