2 Replies Latest reply on Sep 14, 2017 5:41 AM by Jim Wahl

    Calculation of slope of trend line

    Ale Crippa

      Hi all,

      I have a dataset of dates and Event IDs that looks like this:

       

      date                  event id
      01.01.2017       A

      01.01.2017       B

      02.01.2017       C

      02.01.2017       D

      02.01.2017       E

      03.01.2017       F

      04.01.2017       G

       

      I would like to compute a trend line on the number of events.

      Imagine I have a system that logs machinery breakdowns; my trend line would show if the number of breakdowns per month is stable or if it is getting higher and higher.

      Can I do this within Tableau? i.e. without using R as a back-end?

       

      Thanks

      AC

       

      NB I am on Tableau Desktop

        • 1. Re: Calculation of slope of trend line
          Jim Wahl

          The easiest starting point is to use the built-in trend line. See screen shot below.

          2017-09-14_14-24-32.jpg

           

          If you want to show the slope or correlation in the title or tooltip, you can calculate these manually.

          r = correlation:

          r order date and count =

          WINDOW_CORR(ATTR(INT([Order Date])), COUNT([Order ID]))

           

          slope = r * StdDev y / StdDev x =

          [r order date and count] * WINDOW_STDEV(COUNT([Order ID])) /

          WINDOW_STDEV(ATTR(INT([Order Date])))

           

          Note that WINDOW_XXX are table calculations, which are "aggregates of aggregates". The aggregates, in this case, are COUNT(Order ID) and ATTR(INT(Order Date)). The date is converted to an int and then the ATTR() is used to aggregate it. ATTR() is sort of a helper aggregation where Order Date will be returned if there is only one value of order date for the mark.

           

          For this to work, you need order date in the view. Most likely it'll be your x-axis as an "exact date" pill. If you want to aggregate at a different date level (week, month, quarter), you'd need to use the DATETRUNC(period, [Order Date]) function in both the calculated fields and the pills in the view. The period can be parameterized for week, month, quarter, ...

           

          Jim

          • 2. Re: Calculation of slope of trend line
            Jim Wahl

            And if you just want the slope, you could use cov / var.

             

            //slope of trend line = cov(x,y) / var(x)

            WINDOW_COVAR(SUM([Profit]), SUM([Sales])) / WINDOW_VAR(SUM([Profit]))

             

            See: Covariance, Trend Lines, Correlation Coefficient R and R-Squared