5 Replies Latest reply on Apr 24, 2019 11:04 AM by Paul Albert

    Calculating the slope (exponent) of a power law trend line

    Paul Albert

      I've been trying to follow the following thread - Covariance, Trend Lines, Correlation Coefficient R and R-Squared , but what I want to do is calculate the slope (exponent) of a power law fit.

       

      Complicating this a bit is that I am doing something like the Superstore equivalent of comparing the COUNTD([Product Name]) with COUNT([Oder ID]).  Both of these are text strings.  The Covar expression seems to hate these aggregations.

       

      Any thoughts on how to calculate what the Power trend line slope would be?  Jonathan Drummey SMMC Jim Wahl

       

      Thanks for any suggestions you could offer!

       

      Paul

        • 1. Re: Calculating the slope (exponent) of a power law trend line
          Jonathan Drummey

          Hi Paul,

           

          Here you go:

           

          Screen Shot 2019-04-23 at 9.29.42 AM.png

           

          The orange line is the power trend that I computed, overlaid on that we can see a grey line that is the Tableau-generated power trend, and the intercept & exponent are shown in the Title.

           

          Note that the pill on Columns is a plain INDEX() table calculationand there's a defined sort on the Product Name pill. This gets the same result as the RANK(RUNNING_SUM(COUNTD(Product Name))) without the extra table calculations.

           

          For the power trend followed the methodology outlined in https://www.real-statistics.com/regression/power-regression/ and in the attached workbook I did the following:

           

          1) Created natural log versions of the X & Y measures.

          2) Used the table calculation functions from the Covariance, Trend Lines, Correlation Coefficient R and R-Squared post to compute the Log-Log linear intercept, slope, and trend. You can see this in the log-log start and log-log validate trend views.

          3) The Power slope/exponent beta is then the same as the Log-Log linear slope. However the Power intercept is e^a (aka EXP([Log-Log linear intercept]) in Tableau terms) and then Power Trend Y has the formula y = e^a * x^b. I implemented those in the view validate trend that you can see in the screenshot above.

          4) The R-squared of the Power Trend is Covar(ln x, ln y) so I also built that.

           

          Hope this helps! At some point I'll add these instructions back into the covariance thread.

           

          Jonathan

          2 of 2 people found this helpful
          • 2. Re: Calculating the slope (exponent) of a power law trend line
            Paul Albert

            Jonathon,

             

            Thanks for helping me work through this.   Your expertise and assistance are greatly appreciated.

             

            I'm going to study your approach hard as I continue trying to puzzle out my other big challenge (Calculating a proper Gini Coefficient as an LOD(?) ).

             

            This has really made my day.

             

            Cheers!

            • 3. Re: Calculating the slope (exponent) of a power law trend line
              Paul Albert

              Hi All,

               

              So this solution does work in the aggregate.  What I'm hoping to do next is allow comparisons of slopes among higher dimensions such as sub-categories.

               

              For example, this solution shows the following for Appliances:

               

              Screen Shot 2019-04-24 at 12.18.42 PM.png

               

              And this for Tables:

               

              Screen Shot 2019-04-24 at 12.20.04 PM.png

               

              Yet, when I try to compare the slope for Sub-Categories, this is what I get:

               

              Screen Shot 2019-04-24 at 12.21.31 PM.png

               

               

              I'm cringing at asking what I believe is a simple question, but how do I get Tableau to calculate the slope for the different Sub-Categories?  I'd want -0.50 for Appliances  and -0.46 Tables here.  Any suggestions?

               

              Much thanks.

               

              -- Paul

              • 4. Re: Calculating the slope (exponent) of a power law trend line
                Jonathan Drummey

                Hi Paul,

                 

                With table calculations many questions are not simple. A way to think about Tableau's table calculations is that the "table" they are computing over is based on the pills in the view. So even though we start out with a Tableau data source what the table calculations are computing over is the (usually smaller) set of query results that are created by the dimension(s) and measure(s) in the view. In the original views I built the Product Name dimension was on detail (with a custom sort) so the table calculation had a compute using on Product Name. In the two screenshots of the filtered views the compute using was still on Product Name, only the available data had changed due to the different filter settings.

                 

                In the third screenshot you provided the only dimension in the view is Subcategory. So the table calculations are computing based on only that dimension and with the particular Compute Using the view started with they were returning the same result for all sub-categories.

                 

                There are two parts to this problem:

                 

                1) In order for the table calculations to accurately compute we need to increase the viz level of detail (the level of detail created by the dimension(s) in the view) to include the Product Name dimension. Again, this is because table calculations only work based on the viz level of detail, not based on the underlying data. (And we have to use table calculations (instead of LOD expressions) in this case due to the use of Index on the X axis, there's no easy way to compute that without a table calculation at this time).

                2) Then we can set the Compute Using of the calculations to be on Product Name, and by default partition for each Subcategory:

                 

                Untitled 3.png

                 

                3) This not only returns the accurate number, but returns an accurate number for each & every product in each sub-category. Therefore the next step is to set up a field to work as a "late filter" to be applied after the slope/exponent calculation is computed. The FIRST() table calculation returns 0 for the first mark in the partition, -1 for the second mark, and so on. This also has a compute using on Product Name. (I used a type in calculation for this).

                 

                4) Then the FIRST() pill can be moved to the Filters Shelf and in this case I just filtered it for at least = 0 so it only keeps one mark for each sub-category:

                 

                Untitled 4.png

                 

                v2019.1 workbook is attached, let me know if you have more questions!

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Calculating the slope (exponent) of a power law trend line
                  Paul Albert

                  Thanks so much for such a helpful guide to understanding Tableau mechanics.

                   

                  If I could do cartwheels, I'd be doing them!

                   

                  Now, on to applying your helpful insight to my real dataset.

                   

                  To flesh out the thread, the slope of the power fit line is reporting the relative variety richness of the sub-categories (as measured by count of items by orders).  The lower the slope, the more diverse and the higher the slope, the less diverse the relationship.

                   

                  Cheers!