Hello, I need some help with a derived field. I have a report I am working on for FSA benefit contributions, and determining if employees are going to meet or exceed their annual pledge amounts.
I included pay frequency (weekly, monthly, semi-monthly and bi-weekly) Based on the pay frequency I created a derived field to calculate the # of checks per year. (weekly =52, biweekly = 26, semi-montly = 24 and monthly = 12)
I have the dollar amount contribution per check, annual pledge amount, as well as the YTD contribution amounts.
I added the following derived fields:
YTD / Amount per check = # of checks issued this year
# of checks per year - # of checks issued this year = # of checks remaining
# of checks remaining * Amount per pay period = Amount to be taken through the rest of the year
YTD amount taken + Amount to be taken through the rest of the year = Total
Then I have the two columns (Annual Pledge & Total) highlighted to compare and see if the employee will meet or exceed their Annual pledge.
The problem is, any new hires that have not been here the full year for 2005.
So I have an employee who is semi-montly (24 checks per year) BUT she was hired on 3/7/2005. My report shows her as 4 checks taken, with 20 remaining, which is incorrect.
Is there a way to add a formula or derived field to say:
If hire date is > 1/1/2005 …then calculate the # of weeks remaining to get an accurate total?
I included pay frequency (weekly, monthly, semi-monthly and bi-weekly) Based on the pay frequency I created a derived field to calculate the # of checks per year. (weekly =52, biweekly = 26, semi-montly = 24 and monthly = 12)
I have the dollar amount contribution per check, annual pledge amount, as well as the YTD contribution amounts.
I added the following derived fields:
YTD / Amount per check = # of checks issued this year
# of checks per year - # of checks issued this year = # of checks remaining
# of checks remaining * Amount per pay period = Amount to be taken through the rest of the year
YTD amount taken + Amount to be taken through the rest of the year = Total
Then I have the two columns (Annual Pledge & Total) highlighted to compare and see if the employee will meet or exceed their Annual pledge.
The problem is, any new hires that have not been here the full year for 2005.
So I have an employee who is semi-montly (24 checks per year) BUT she was hired on 3/7/2005. My report shows her as 4 checks taken, with 20 remaining, which is incorrect.
Is there a way to add a formula or derived field to say:
If hire date is > 1/1/2005 …then calculate the # of weeks remaining to get an accurate total?