community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Compare Every Row in a group to every other

Asteroid

Hello Alteryx Heads,

 

I have been tasked with taking a data set of 6 Fields and 149 Records and comparing values. Theses are grouped into Bands with different codes in the same bands and a salary value for 4 years. I need to compare each salary to each other salary in the same band and compare these differences year on year. This is going to take me ages. Does anyone know a quick way to do this? Is there an analytic tool i haven't used that can achieve this?

 

Example:

 

 

A759£45,245.00£48,950.00£43,076.00£41,541.00
A715£52,852.00£52,726.00£59,613.00£58,612.00
A527£55,803.00£41,728.00£34,504.00£44,046.00
A541£46,348.00£51,603.00£39,014.00£54,787.00
B720£59,906.00£46,548.00£54,313.00£36,880.00
B693£48,043.00£42,537.00£55,951.00£36,890.00
B648£39,096.00£43,834.00£41,336.00£59,496.00
B681£43,713.00£34,425.00£31,321.00£48,054.00
C776£41,562.00£50,610.00£38,747.00£42,992.00
C580£35,617.00£58,479.00£59,578.00£45,753.00
C559£50,331.00£52,105.00£31,481.00£46,388.00
D596£58,711.00£58,842.00£40,744.00£51,731.00
D596£53,066.00£37,320.00£55,965.00£43,904.00
D689£34,468.00£34,673.00£48,709.00£47,713.00
Asteroid

When you say compare them within bands and year on year....what should emerge from it?  What ultimately are you wanting the output to be?  If you can take one band from your sample data and reference that here, it would be really helpful.

Asteroid

Take a look at attached and let us know if this is what you're trying to accomplish. It compares each person's salary for that year to the average salary of everyone in the same band for that year..

Asteroid

Thanks @Jim7 and @Storm

 

I managed to achieve what i wanted, i didn't explain very well. I had to do a Cartesian join with the append tool, transpose the result, summarize, Cross tab and then use a multi line formula.

 

I will post an example when i have time

 

Cheers

Asteroid

Great to hear that you you were able to solve for it, sounds like you literally had to compare each employee to each rather than just to the average.

Asteroid

@Jim7 It was a complete nightmare but its one now.

Could you please provide the solution analytically? I am a beginner and I'm trying to solve the same problem.

Asteroid

Hi @amponas21 

 

From what i remember from this i appended the main data set to itself to give every possible combination. I then transposed this to give Name Value Groups and then grouped by these. i will try and see if i can find an example workflow later if i have time. 

Labels