Skip to main content

Hello Pigment Community,

 

I need your help on calculating merit increase for existing employees.

 

To give you more context, our merit increase calculation is made of:

  • 1. The annual package of an employee → this is the basis for our calculation.
  • 2. The merit increase % : it will be applied to the annual package and is cumulative through the years.
  • 3. The merit increase eligibility: we calculate it based on the start date of the employee.
  • 4. The timing / effective date of the merit increase: it is the month the merit increase will be effective.

We are currently stuck on the last part, which is the timing of the merit increase.
I will explain quickly each component below and will explain more in details why we are stuck on the effective date and what we try to achieve.

 

 

1 : The annual package of an employee.

Let’s say we have an employee making 40,000 EUR a year. 

We have a metric in which we have 40,000 EUR on every month for this employee.

 

 

2 : The merit increase %.

It is defined by year and it is cumulative.

 

Here we input 4% of merit increase for each year, and then we calculate what is the cumulative % that we need to apply on these 40,000 EUR.

 

 

3 : The merit increase eligiblity.

We calculate it by checking if the employee started before a given number of months.

 

4 : The timing / effective date of the merit increase.

This is where we need your help.

 

In our current formula we say:

IF(Merit Increase Eligibility = TRUE,
Annual package * Merit Increase% cumulated rBY CONSTANT: Month.Year],

Annual package)

 

Currently, we are just testing if the merit increase eligibility is true, and if it is, we are applying the merit increase % that we defined for the year.

 

In this formula, the effective date of the merit increase is the beginning of the year by default as we are doing a BY CONSTANT: Month.Year].
I want to improve that formula and to be able to also set the timing / effective month of the increase.

 

I created a metric “Month of Increase” where I can chose “January” , “February”, etc. for each year.

 

 

Could you please help us integrate this effective date in our formula for each year?

One of the complexity of it, is that if we chose to set the increase in FY25 in June, we still need to apply the % of FY24 to FY25 salaries until June, and then take into account FY25 increase starting in June.

 

Please let me know if you need any detail.

 

Thank you,

Steven

Hello Steven,

 

If I understood correctly, you want to take into account the Raise Start Month to start applying the Yearly raise.

 

I looked into something that should work in two steps.

 

First step: Display the Yearly raise starting from the Raise Start Month

We will use the Yearly raise but add a filter to only keep the value for the Month equal to or higher than the Month of Year that is specified. The formula should look like this:

 


We can create a metric for the result of this formula with the name: mAsm] Yearly raise by Raise Start Month

 

Second step: To apply the yearly raise of the previous Year for the months before this Raise Start Month we can use the Previous() function. the formula will look like this:

 


You might need to adjust the formulas to use the appropriate metrics, but the logic should work.
Let me know how it goes

Hi @Steven Congar - did Gawain’s answer above get you what you need? 🤞

Hi Karl and Gawain,

Sorry I didn’t have time to go over it and test it as I was out. I will look into it and let you know if it works. I’ll flag it as best answer for now and will come back to you if needed. 🙂

Thanks Gawain for the detailed answer!
Steven

Reply


Ask your question here!

Terms & ConditionsCookie settings