Many times, Tableau is used beyond data visualization. Often we need to perform all sorts of functions. Actually, Tableau is a powerful calculator. Instead of using another tool, such as Python or Excel, we can do it in Tableau proper. Here is a case a colleague asked for: calculating Spearman's Rank Correlation.

For the theory behind Spearman's Rank Correlation, please refer to its Wikipedia page. The key idea of Spearman's is, instead of calculating the correlation between the raw values of two data series or Pearson correlation, we calculate the correlation between their ranks in respective series. In other words, Spearman's correlation is equivalent to Pearson correlation of ranks. 

Above, d is the difference between the ranks of each data sample in their respective series. n is the number of data samples.

We use the superstore data set to give an example of calculating the Spearman's Rank Correlation, between quarterly Sales and Quantity. Here is the table that shows the process. 

To begin with, we need to calculate the ranks of Sales and Quantity. Note we need to use Rank_Unique() function by which some equal values are assigned different by adjacent ranks.

We presented here two approaches to the following calculation. 
 
One is based on the native Tableau function Window_Corr() which is the Pearson correlation. This makes the calculation really simple. 
The other is based on the math definition. 
Note that both are computed along Order Date.

The last two columns show the identical correlation coefficient 0.96 which means that the two measures are highly correlated.
The key pseudo formula in Tableau are shown as above. To see the details, please go get the workbook.

Feel free to leave comments below or contact me at twitter @aleksoft.
0

Add a comment

[Forward: I asked ChatGPT o1-mini who then wrote this. Hope it helps. All the credit and the blame go to ChatGPT.

I went over the plan and it looked decent. Whether it can be done in 30 days or not, it depends on the person and the time he spends on it.
Just came back from Tableau Conference 2022 at Las Vegas. What an exciting event! The most exciting thing is reuniting with old friends and meeting with the datafam people known online for years.

Attended first time the Tableau Visionary summit.
A little enhancement in the formula editor can make a big difference for whose who create formula all the time in Tableau. Here are my wishes for a future editor. 

Highlighting Syntax Words

Currently a formula in Tableau can look plain and a bit uninspiring.
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.