14 Replies Latest reply on Sep 23, 2019 7:47 PM by Luke Bullard

    Trend line slope (and p-value)

    . Andrew26

      Is there a way to create a text field listing the slope of trend lines?  (I am much more interested in the slope, but if it is possible, I'd like to know if p-value is also available as a text field)


      I have many different customers purchasing habits over time in a line graph with trend lines.  I'd like to create a table showing the trend lines' slopes (and possibly p-values) so they can be sorted in the table by what business is declining and what is growing.



        • 1. Re: Trend line slope (and p-value)
          Michael Cristiani



          Do you want to do this automagically or one trend line at a time interactively?  There are some ways to do this kind of thing, but the route depends on how the visualizations will be consumed or used.  Can you provide a bit more information?



          Peace and All Good!

          Michael W Cristiani

          On Twitter

          • 2. Re: Trend line slope (and p-value)
            . Andrew26

            Thanks for replying, mcristia.


            The trendline slopes I'd like to see are those of about 55 different customers which are shown on one line graph (I know that's a lot for one graph, but I have it set up so they're highlighted when I click in the adjacent table)


            I know I can hover over the trendline and it shows the slope, and I know you can describe the trend model by right clicking on the chart.  However, this is not useful to me.


            What I want is to have a calculated field that presents the all 55 trendline slopes as numbers next to in a table.



            • 3. Re: Trend line slope (and p-value)
              Brandon Smith

              I wanted to bump this as well because I have a similar scenario.  I am trying to create an optimal pricing model so if I use the base trend line it's no good because my customers with poor margins bring down the trend.  I would like to be able to adjust and calculate a line and plug in that formula and at the minimum have Tableau draw it for me (at maximum be able to use it in calculated fields).  Right now I am having to export it out as an image and using a photo editor to draw the line. 

              • 4. Re: Trend line slope (and p-value)
                Joe Mako



                With custom table calculations, I believe what you are looking can be done, but you would need to provide sample data, and the formulas and/or business logic you want applied, and what you expect for a result. With additional details and specifics on what you have and what you are looking for, assistance can be provided.

                • 5. Re: Trend line slope (and p-value)

                  Hi there,


                  I want to bump this thread - did any of you find a solution?


                  What I want to do is to "fetch" the last value of a trendline and use it in a calculated field.


                  As far as I can tell I would somehow calculate the field myself, if I know how the trendline is calculated, right?


                  So if I use "Describe the Trend Model" I get the "Model Formula" which I do not understand. It multiplies some values and then adds an intercept.


                  Is there a way to logically derive the necessary structure of a calculated field from a trend line? Where would I start?




                  • 6. Re: Trend line slope (and p-value)

                    Any news on this one? :)

                    • 7. Re: Trend line slope (and p-value)

                      So I reckon no?


                      Would it help to contact customer support?

                      • 8. Re: Trend line slope (and p-value)
                        Joe Mako



                        does: http://www.alansmitheepresents.org/2011/06/fitted-curve-modeling-in-tableau.html help your situation?


                        or are you looking for something like: The specified item was not found.


                        or do any of the comments at http://www.tableausoftware.com/support/forum/topic/forecasting-how address your situation?


                        If these do not help your situation, then if you can provide a sample packaged workbook that represents your situation and detail what you expect for a result, an example solution can be made.

                        • 9. Re: Trend line slope (and p-value)
                          Philip George



                          I saw one feature which helps for advanced analysis in the below link.




                          I think this feature can help us for doing prediction also.Am I correct?



                          • 10. Re: Trend line slope (and p-value)
                            Andrew Hill



                            There seems to be a lot of interest in being able to calculate the slope for a linear regression in Tableau above and beyond the built in trend line. This makes sense as sometimes you might want to sort or isolate things with certain slopes. I would give this calculation a shot:






                            Here your [x] field is your x axis and your [y] field is your y axis.


                            For time series it might be a little more difficult. If you have data by day, I might try making your [x] field something like: datediff('day',today(),[Date]) assuming you don't have data going out into the future. If you do, you can just anchor the datediff calc to a random date in the past before your data starts (datediff('day',date(1/1/1998),[Date])).


                            EDIT: or you can just be smart and create a field float([Date]) and use that instead


                            This should pull out your slope value. You can back into your intercept and rsquared with similar logic.


                            Remember that this is a table calculation so if you are looking to slice this up by a dimension will most likely need to edit your table calc to restart every [dimension] (and change the compute using).

                            • 11. Re: Trend line slope (and p-value)
                              Joey Minix

                              Anyone finding this by searching and wants to see how to calculate slope of a trendline using LOD expressions:


                              I'm mimicking this formula to obtain slope: y = αx + β

                              where α = (n∑(xy) -∑x ∑y) / (n∑x^2 -(∑x)^2 )


                              ({fixed:count([X])}*{fixed:sum([Y]*[X])} - {fixed:sum([X])}*{fixed:sum([Y])}) /


                              Where [X] is your x-axis, [Y] is your y-axis


                              If your x-axis is a date, create a calculated field of float([X]) called floatdate.  Substitute [X] in the formula with your new calculated field, like so:


                              ({fixed:count([X])}*{fixed:sum([Y]*[floatdate])} - {fixed:sum([floatdate])}*{fixed:sum([Y])}) /



                              Obviously, if you need to edit the level of detail, add the necessary fields between 'fixed' and the colon.

                              2 of 2 people found this helpful
                              • 12. Re: Trend line slope (and p-value)
                                Kris Erickson

                                Joey,  This method worked splendidly.  Thank you for your contribution to the community.


                                The only thing is that I think it does not handle situations where my LOD has NULLs, but that's on my end.

                                • 13. Re: Trend line slope (and p-value)
                                  Michael Monoski


                                  I have a very similar case, but can't seem to get the formula to provide the right answer.

                                  I'm attaching a sample .twbx

                                  For the data that's in there, Tableau is calculating a slope of .224237 when I bring in a linear trend line. I want to create a calculated field that will return that same result, but I have been unable to get the same result using your guidance above. I think it might be related to fact that my Y value is already an AGG function. If you could take a look, I would really appreciate it. Let me know if you have any questions.


                                  Y should be Unit Price

                                  X should be Year

                                  *I have multiple records in a given year, so that's why my unit price is already a AGG function: sum([Net value])/SUM([Order Quantity])

                                  • 14. Re: Trend line slope (and p-value)
                                    Luke Bullard

                                    Here's a template based on Joey's that lets you run an IF statement on each row to determine if it should be included. Just replace <EXPRESSION> with your expression. If you can throw your dimension in the LOD expression instead of the IF statement, do that as a first option.



                                      {FIXED : COUNT(IIF(<EXPRESSION>,[X],NULL))} * {FIXED : SUM(IIF(<EXPRESSION>,[Y] * [X],NULL))}

                                    - {FIXED : SUM(IIF(<EXPRESSION>,[X],NULL))} * {FIXED : SUM(IIF(<EXPRESSION>,[Y],NULL))}

                                    ) /


                                      {FIXED : COUNT(IIF(<EXPRESSION>,[X],NULL))}

                                    * {FIXED : SUM(IIF(<EXPRESSION>,POWER([X], 2),NULL))}

                                    - POWER({FIXED : SUM(IIF(<EXPRESSION>,[X],NULL))},2)