-
1. Re: Calculating the slope (exponent) of a power law trend line
Jonathan DrummeyApr 23, 2019 6:41 AM (in response to Paul Albert)
2 of 2 people found this helpfulHi Paul,
Here you go:
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. Re: Calculating the slope (exponent) of a power law trend line
Paul Albert Apr 23, 2019 6:01 PM (in response to Jonathan Drummey)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 Apr 24, 2019 9:30 AM (in response to Jonathan Drummey)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:
And this for Tables:
Yet, when I try to compare the slope for Sub-Categories, this is what I get:
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 DrummeyApr 24, 2019 10:24 AM (in response to Paul Albert)
1 of 1 people found this helpfulHi 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:
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:
v2019.1 workbook is attached, let me know if you have more questions!
Jonathan
-
5. Re: Calculating the slope (exponent) of a power law trend line
Paul Albert Apr 24, 2019 11:04 AM (in response to Jonathan Drummey)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!