Removing Rows of Data

Hello,

I am trying to match payment rates to payments based on effective dates. I created some formulas and multirow formulas, but my data is not coming out properly.

Employees can change rates, sometimes 0, 1, 2, 3, or more times throughout the year and I need to ensure that the accurate rate is being multiplied to the payroll qualified compensation, and then that calculation will be compared to the payroll EE contrib % number. I created my first formula to use the first rate and disregard the succeeding rate when appropriate, but I am having trouble trying to remove the preceding rate when only the latter should apply.

I attached my current workflow; there were 1826 payments, so my end filter should have 1826 true rows not 2121.

Thanks,

Discussion Board Data.xlsx

Discussion Board.yxmd

Preparation

Accepted answers

All comments

IraWatt

Hey @cengleman,

I'm trying to follow the process, could you possibly give a shot example input and what you would expect to have as a result?

Thanks,

Ira

cengleman

In row 6, I no longer want the first rate, but I want the second rate applied until the next name appears. Sample text there should be two nulls back to back when the applied rate switches from 1 to 2. Currently, I cannot figure out how to make EE Calc null for the first rate when I no longer want it. This needs to be applied for names/payments with up to 7 rates changes as well.

cengleman

Let me know if you need anymore information

ddiesel

Hi @cengleman!

I've had a read through all of the above, and I'm unclear what the logic should be for the calculations. Will you please clarify? I'm sure we can help.

cengleman

I provided another marked image and Alteryx file example of what I am trying to create. My goal is to use the nearest previous date and rate applicable for each payment. The rate changes periodically and I want to use the most recent date of rate change prior to the last payment made by the individual. In the example above on row 596, if the date of the payment is 2019-04-26, I want to use the nearest previous rate which was chosen at 2019-04-25 and remove the following two rows since those rates do not apply to this payment/payroll date. Additionally on rows 605-607, the effective date is 2019-05-28 for a payment on 2019-06-07, so I need to remove rows 605 and 606 because those rates are no longer applicable. My data currently contains all rates and dates for transactions, so I am trying to eliminate non-applicable rates/dates for the same transaction. I then multiply my TPA % Deferral rate by the payroll qualified compensation, Payroll EE Calc column, to see if it matches provided data in the row Payroll EE Contrib %.

Date_Change_Calculation.yxmd

Marked Data.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703