Contractor Conversions to Employees Calculation

[JonaV]

Below I have a sample explanation of my data. I'm looking to see how many contractors converted to employees week over week. I figured out the formula in excel, but I had to create 3 different columns to find the answer. Below are the details and question. If anyone could help me figure this out I would greatly appreciate it

[JosephSerpis]

Hi @JonaV I mocked up a workflow let me know what you think?

[JonaV]

@JosephSerpis I don't think the way the multi row formula is set up here works, especially with the row+1 in the expression. My data is not sorted in any order, so i doubt using the row+1 will work. In reality, I have about 105k+ rows of data with the ID's spread out in no specific order. I set it up that way in the sample in the picture I attached just as an example.

I've never used the multi row formula before since I'm fairly new to Alteryx. I've just been able to understand it quicker because I have background knowledge of databases and working with Excel

If there is another way to figure this out and/or need other pieces of information, please let me know

Thanks

[JosephSerpis]

Hi @JonaV I revised my example solution and add data in a unsorted manner so I am sorting by ID and Date then using a multirow tool to do the Employee conversion.

[JonaV]

@JosephSerpis looks like your data has more than 2 dates. Also, the data should actually be sorted by date descending and the data type in the output should be int16 so I can add up the 1's (conversions) and ignore the 0's.

I ran the workflow the way you set it up, but it still did not work

I updated the image in the post in hopes it makes more sense.

Any other workarounds?

Thanks!

[JosephSerpis]

Hi @JonaV I revised the workflow. If it does not work with your data could you please provide a sample file of your data?

[JonaV]

@JosephSerpis I think there should be a way to come up with this calculation with a formula tool only without having use the multi row formula and sorting and selecting.

I have already been able to figure out this formula in Excel, but I had to break it into 3 different columns that each have a different formula just so that I could process it better myself. I can put these 3 formulas together as a nested formula to make it 1.

My issue is translating this formula into Alteryx since it uses different syntax than Excel

My formula is as follows:

Column 1: =COUNTIFS(Column (ID), Cell (ID# in column (ID)),Column (Worker Type), "E") - this counts the instances of E's per ID#. There are only 3 outcomes since the same ID# can only repeat twice max, these are 0,1,2. 0 means that the ID# did not have an E associated with it (meaning it was a C). 1 means that there was only 1 E for that ID# (meaning that this could be either a new hire (hired in the current week), termination (terminated the prior week), or a conversion (moved from being a C to being and E, which is what I'm looking for)

Column 2 = IF(AND(COUNTIFS(Column (ID), Cell (ID# in column (ID)),Column (Worker Type), "C"),Cell (worker type in column (worker type))="C"),1,0) - this counts if the ID# is associated with a C in either the current week or the prior week. If true then 1 if not then 0. if it returns a 1 for the prior week, it is possible that the ID# became an E in the current week, or that it stayed the same the current week (as a C), or that it was terminated the current week

Column 3 = IF(cell (Column 1) = cell (column 2),1,0) - this just means that if the cell in the column 1 formula returns a 1 and the cell in the column 2 formula returns a 1 it is a contractor conversion if not it is a different situation

[JonaV]

@JosephSerpis See below an updated screenshot with all 3 columns i have formulas in to find the 1 highlighted in green under the "Conversion" column.

I've also attached the Excel sheet so you can see the formulas in Excel

Thanks for the help!

[JosephSerpis · Accepted solution]

Hi @JonaV this problem won't be solved with a single formula tool in Alteryx. Firstly the Alteryx formula tool does not have a countif function as fundamentally the formula tool works at a single row at a time. So unlike excel you can't have a formula for a range then extends beyond the current row. This is why thus far I have used multi row tools and other data preparation steps. I have mocked up a workflow that mirrors your logic that you provided in your excel however you can see I have taken a number of steps and not just used one tool to achieve this.  

[JonaV]

@JosephSerpis this works! Although, in the formula towards the end of the workflow I would add a "AND [Right_Worker Type]=C" to isolate only the C's that became E's. Once this is done and the output is exported to Excel then in the Conversions column we can filter to only show 1's and these are the total Contractor Conversions

Thank you so much for your help Joseph!