13 Replies Latest reply on Jun 16, 2016 9:24 AM by Michael Monoski

    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.

       

      Help?

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

          Andrew,

           

          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?

           

          MANY BLESSINGS!

          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.

             

            Possible?

            • 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

                Brandon,

                 

                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?

                   

                  Thanks,

                  Peter

                  • 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

                        Peter,

                         

                        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

                          Hi,

                           

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

                           

                          http://community.tableau.com/message/181143#181143

                           

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

                           

                          Philip

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

                            Greetings,

                             

                            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:

                             

                            (size()*window_sum(sum([x])*sum([y]))-window_sum(sum([x]))*window_sum(sum([y])))

                            /

                            (size()*window_sum(power(sum([x]),2))-power(window_sum(sum([x])),2))

                             

                            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])}) /

                              ({fixed:count([X])}*{fixed:sum(power([X],2))}-power({fixed:sum([X])},2))


                              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])}) /

                              ({fixed:count([X])}*{fixed:sum(power([floatdate],2))}-power({fixed:sum([floatdate])},2))

                               

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

                              1 of 1 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

                                  Joey,

                                  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])