We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

Finding % Commonality

jfoley020
5 - Atom

Hello, I'm trying to figure out how to find % commonality between cells within the same column. 

 

For example: Take the list below, I want to find what % do each of the services share:

 

Tree Service received 4 requests (A,B,C,D) and it has a 50% commonality with House (both serviced Request A, B), 75% commonality with Lawn (A, C,D) and a 50% commonality with Carpet (B,D). 

 

Then I want to filter these commonalities to answer this question: Which service worked on 75% of the same requests as the Tree service- the answer being "Lawn"

 

jfoley020_0-1667512182135.png

 

Thank you!

 

2 REPLIES 2
DataNath
17 - Castor

Hey @jfoley020, I've put something together to prove the concept here - think it seems to be along the lines of what you're after. The idea here is that we:

 

1) Isolate the [Service] of interest i.e. Tree here

2) Create concatenated lists of the [Request] for each of the other [Service]

3) Append these to Tree

4) Check if each Tree [Request] is contained within the concatenated list or not, assigning a 1 to the match count if so

5) For each Tree <> Other [Service] combination, sum up the match count and divide it by the number of requests for tree to get the percentage

 

DataNath_0-1667516058175.png

jfoley020
5 - Atom

It worked perfectly, thank you!