Smart Tile calculations

[pikesway]

Can anyone explain the math behind the Smart Tile?  I am using the example data provided in the help and attempting to recreate the ranges using Excel.  I'm doing this because I am being asked to 'prove it' that the tool is a good replacement for SPC charts and can help us create alerts (when things fall into the 'Extreme' Tile name bucket.  

If anyone can help me understand the math used so I can recreate in Excel and/or explain it to the stats people in my organization and put their minds at ease I'd appreciate it.  Been messing with it for a while and can't figure out what it's doing.  

Thanks!

[JessieC]

@pikesway - https://help.alteryx.com/current/designer/tools/preparation/tile-tool

Creates tiles based on the Standard Deviation of the values in the specified field. The tiles assigned indicate whether the record's value falls within the average range (=0), above the average (1) or below the average (-1), etc.

Sort records by variance from the average

The tile value represents the mean of the data range. The tile numbers explain the range directly. The range always start at -0.5 and end at +0.5:
 

Title Range

0	-0.5 to 0.5
-1	-1.5 to -0.5
1	0.5 to 1.5

Output Verbose Name field: In addition to the descriptors mentioned above, the value range that the tile indicates is listed in parenthesis.

In the Tile Example - you can see what the ranges are from selecting Output verbose name column

[pikesway]

I did see this...but this does not get to the math behind the calculations.
Using the verbose name and the test data set Alteryx produces a column called SmartTile_Name. The values in the column are:
Above Average (61 to 160)
Average (23.25 to 61)
Below Average (8.8 to 23.25)
Low (3.35 to 8.8)
Extremely Low (Below 3.35)

Based on some set of rules I should be able to calculate Standard Deviation of the values in that data set and get 29.74867 and come up with the values in the range (23.25 to 61, for example).

That's the math I'm looking to duplicate. Where/how does Alteryx come up with that range. It states it's based on Std Dev. Calculating that is simple enough in Excel...just not sure what it does with the Std Dev to get that range.

Any help/thoughts would be appreciated.

[KostjaPwC · Accepted solution]

Hello Pikesway ,

Based on this post long time ago I think I kind of figured out what Smart tile does:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Smart-Tile-further-explained/td-p/4376

Basically, if the data has no negative values, Alteryx assumes normal distribution for logarithms of data instead of actual values. 

Tried it out in excel and got these results:

......................

The numbers seem to be in a similar range.

There is still a problem with these results as there is no log of 0. Right now I excluded these [Null] values from the mean and standard deviation calculations, but it might be the reason why I get slightly different numbers.

I still haven't figured out what Alteryx does with those numbers that it can't take logarithm of, but hope this reply helps. 

I am also attaching the excel file, so you can test further on it.

All the best,

Konstantin

[pikesway]

Thanks so much Konstantin!  That is helpful.  I'm going to mess with it today.  Thank you!