
1. Re: how to extract trend line information?
Ramon Martinez Oct 15, 2013 6:34 PM (in response to Paul Reichl)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 rightclick 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 rightclick 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

trendline_information.twbx.zip 23.5 KB


2. Re: how to extract trend line information?
Paul Reichl Oct 15, 2013 8:11 PM (in response to Ramon Martinez)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 Oct 15, 2013 9:53 PM (in response to Paul Reichl)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 Oct 15, 2013 11:39 PM (in response to Ramon Martinez)1 of 1 people found this helpfulYou 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]
pvalue (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

5. Re: how to extract trend line information?
Paul Reichl Oct 16, 2013 6:19 PM (in response to Jim Wahl)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 Oct 16, 2013 7:24 PM (in response to Paul Reichl)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 Oct 21, 2013 9:40 PM (in response to 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 Oct 21, 2013 11:45 PM (in response to 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 Oct 21, 2013 11:54 PM (in response to Paul Reichl)Hi Paul,
Good to know that the calculation of Rsquared and pvalue using R integration with Tableau 8.1 is ok.
Best,
Ramon

10. Re: Re: how to extract trend line information?
Jim Wahl Oct 21, 2013 11:58 PM (in response to Paul Reichl)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
You can get a nearly identical coeff table from R using the summary() function. (The pvalue is very small, but the tvalue 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))
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 rsquared, adjusted rsquared, ...
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 RStudio installed, it's probably a must have for debugging the functions before using them in Tableau. There are alternatives to RStudio, but it's free and very well supported.
Jim