Customizing formula to avoid using string data

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!!

Data Investigation

Accepted answers

darenson

@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!

All comments

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.

july-total.yxmd

darenson

@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.

Changed to:

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703