Customizing formula to avoid using string data

[darenson]

I am not sure if Alteryx has this capability but I sure hope it does. I am trying to take payroll data that is broken down into different pay periods of hours worked. I have been trying to come up with an alterx formula that would automate the following process:

Ie) 

	07/06/2019	07/20/2019	08/03/2019	July Total
Person A	254.5	159.75	135.09	367.069
Person B	225	118.67	121.83	303.702

July = (6/14)*254.5 + 159.75 + (11/14)*135.09 = 367.069

Since the first date column starts with 6, it would be 6 days out of the biweekly pay period (06/14) + the full pay period of 07/20 (100%) + a portion of the biweekly pay period ending on aug 3rd (14 - 3 =11).>> 11/14

Basically, what I am asking here is if it's possible to come up with a formula that takes a portion of column A, the full amount of hours from column B and a portion of hours from column C. 

Any help with this would be very appreciated!!

[MichalM]

@darenson 

Using the formula that you provided, I get a different July total 

July = (6/14)*254.5 + 159.75 + (11/14)*135.09 = 374.964 rather than 367.069

Assuming I'm not missing anything I'd do the following

• Transpose the data as it will be easier to perform the calculation
• Turn the dates into Alteryx friendly format (yyyy-dd-mm)
• Extract the day number and month number from the date
• And build the logic depending on how frequently you run the analysis and how many months is included in your data

In this case I wanted to understand two things

• Is the day number more than 14? If so, I want to take the whole sum
• If it's less than 14, I want to understand whether it's July or August (I use the Summarize tool to get the Min of the dates per person' this logic will work for a monthly process with data containing 2 months) and calculate the proportion

IF [DayNo] >= 14 THEN [Value]
ELSEIF [MonthNo] = [StartMonth] THEN ([DayNo]/14)*[Value]
ELSE ((14-[DayNo])/14)*[Value]
ENDIF

Example workflow attached.

[darenson · Accepted solution]

@MichalM

After checking out your workflow this seems to be exactly what I was looking for. The only issue is when I tried to apply this to my dataset I get this error message:

Which ultimately gives me a completely Null column, when it should be rearranging the date to the desired format.

If it helps, this is what the date was formatted to originally. It starts in this format: Year-MM-DD and then when I ran the Cross Tab function to structure it the way you saw the data from my initial question, it changed to Year_MM_DD

Changed to:

I'm guessing I would have to do some restructuring of the date format at some point, but not sure which path to take for this. Once this is figured out everything else should be good to go. Thank you!

[MichalM]

@darenson 

When you look at the workflow I shared with you you'll notice that I convert the data as I bring it in a format which is MM/dd/yyyy.

If yours comes in as yyyy-MM-dd already, check whether it's a string data type (you can see this in the Metadata tab within the results window, and if that's the case you just need to update the DateTime parse configuration from MM/dd/yyyy to yyyy-MM-dd.

[darenson]

@MichalM

I ended up figuring it out from the last step, so you can disregard the previous message- Thank you!!