1 2 Previous Next 17 Replies Latest reply on Nov 14, 2018 11:30 AM by Shinichiro Murakami

# Formula for R2 and P values for Trend Lines

Hi all,

So I've been following this excellent community for a long time, but this is actually the first question I post, so please bear with me.

In a workbook that shows sales revenue over the last 24 months, I want to highlight negative linear trends (so management can take action). However, I only want to highlight (color) them when the negative trend is relevant enough so we don't go into panic mode unnecessarily.

For this, I found the formula to calculate the slope of the trend-line in this community so I know if it's positive or negative (thanks for that), so that's done. But I'd like to be able to calculate the R2 and P values as well in order to assess the significance of the trend. Understanding you still need to look at the distribution of residuals afterwards, this will at least highlight where there is a certain level of confidence that the trend is really negative and allow me to tell the user to further look into the trend.

A Tableau consultant I asked recommended using R integrated with Tableau, and while this would certainly make it easier, it's not an option in the organization I am in.

So any ideas from Tableau Jedi's?

Thanks!

• ###### 1. Re: Formula for R2 and P values for Trend Lines

The Force wasn't with you

Let me call the Tableau Jedis one more time!

Any Guardian able to help Jordi Buch with his formula?

• ###### 2. Re: Formula for R2 and P values for Trend Lines

Bora may chime in but he mentioned that correlation coeffiient's can be done with LOD calcs.  R makes things easier but it sounds possible in Tableau.

• ###### 3. Re: Formula for R2 and P values for Trend Lines

Hi Jordi,

I put together an example here. I hope it helps.

Thank you,

Bora

2 of 2 people found this helpful
• ###### 4. Re: Formula for R2 and P values for Trend Lines

Hi Bora,

You left me speechless... Exactly what I needed!!!!!

Thank you so much!!!

• ###### 5. Re: Formula for R2 and P values for Trend Lines

While this is incredibly helpful - I am stuck on the Critical Values (per Category) piece. I have a few correlations where the p value < .05 and my "Is Significant" calculated field is saying "False". Do you think this could be due to the Critical Values (per Category) calculated field only includes rows 1-22 from the t-table? Plus, how did you calculate the numbers such as ".000024" in the Critical Values (per Category) calculated field?

I am very close to my goal though.

Many thanks!

Heather

• ###### 6. Re: Formula for R2 and P values for Trend Lines

Hi Heather,

Those coefficients are used to interpolate between points in the table so the slope and the intercept used for interpolation. Can you share how many points you have in the pane and what the critical value calculation returning is?

Thanks,

Bora

• ###### 7. Re: Formula for R2 and P values for Trend Lines

Hi Bora,

Thank you so much for the quick response.

Attached is the .csv file that shows my Critical Values (per Category) calculated field. In my case "(per Category)" translates to "(per Question ID)" for my data. Did I do this correctly?

For your example there are 3 categories, I have about 50+ Question IDs.

The scatter plots are the students' responses to a survey item correlated with their first fall term GPA. Each dot is a student. Each scatter plot has about 300 students represented.

The "Is Significant? (per Question ID)" calculated field is  >  and is returning a "TRUE" or "FALSE".

Now I will say this - the series of calculated fields are working for 3 scatter plots. They are the only scatter plots with a p-value of <.0001 (see jpeg that is attached).

Your feedback is greatly appreciated!

Heather

• ###### 8. Re: Formula for R2 and P values for Trend Lines

Hi Bora - also attached are the slope, intercept, t-statistic, and critical value for each Question ID. Also, I have a very tall data set given that this is survey data and I need to work with both Labels and Values of the survey responses. You think that might be affecting the SUMS within the calculated fields?

• ###### 9. Re: Formula for R2 and P values for Trend Lines

I tinkered all yesterday with the calculated fields. I think part of the problem may be the fact that my X variable (Values) is coming from a tall data set and my Y variable (Term GPA) is coming from a wide data set. Is there some SQL that I can include in the calculated fields? Some sort of COUNTD for Values? Really want to get this accomplished before my meeting with leadership next week to present this data. If necessary, I can de-identify the Student IDs and send you twbx?

Many thanks!

Heather

• ###### 10. Re: Formula for R2 and P values for Trend Lines

Heather,

It's ALWAYS best to include a .twbx when asking a question.  Yes, please anonymize your data and attach it to your post.  This way others can pop in and help.

• ###### 11. Re: Formula for R2 and P values for Trend Lines

Attached is the twbx of my problem. Please help!!!!

< author removed object >

Many many many thanks!

Heather

• ###### 12. Re: Formula for R2 and P values for Trend Lines

Hi Heather,

This is a two-tailed test so to mimic the comparison the table is doing given the significance level (the reference t-table link is provided in the calculated field so you can see what significance the test assumes by looking at the table and corresponding upper tail probability) you have to multiply your p-value by two.

0.02514 * 2 = 0.0528

0.0528 > 0.05

If you would like this to be considered significant you can change the confidence level the test is running.

Thanks,

Bora

• ###### 13. Re: Formula for R2 and P values for Trend Lines

I'm sorry Bora Beran ! I'm trying to follow the table at this link https://www.stat.tamu.edu/~lzhou/stat302/T-Table.pdf  , but where did the .02514 come from? It's not in the table? If anyone can please help me with resolving my p-value issue I would GREATLY appreciate it!!

• ###### 14. Re: Formula for R2 and P values for Trend Lines

That is the value you circled in your screenshot as the p-value in the tooltip for the trendline. The table is for tcritical values. The relevant piece you will see in the table is that 0.95 is associated with 0.025 not 0.05.

1 2 Previous Next