Formula Fields Overview
Insperity Organizational Planning can be configured to add custom formula fields that work with your data to create powerful insight to your company’s charts. Formula fields leverage the existing information in your data source and the attributes associated with each employee such as start date, birth date, gender, or EEOC category.
The most common formula fields include headcount, which calculate based the number of employees in the chart, or salary totals, which calculates the sum of all the salaries within a range.
Adding a Formula Field
Navigate to Administration (1), then to Fields (2). Once in Fields, click on Add Formula (3).
Complete the following fields under Field Properties:
| Display Name: Type the name you would like to call this field. This will also be the name of the field that will display by default on the box when adding to a view. | ||
| Description: (Optional) Type any descriptions for the formula field that will be helpful when reviewing the formula. | ||
| Categories: Select the formula Category type Number – Use when you will be creating a formula based on numbers. Example, “Total Salary” Date– Use when you want to use a date field to make the calculation. Example, “Days from hire” Lookup and Reference– Use when you need OrgPlus to look up data in your data source. Example, “Headcount” Text – Use when you are creating formulas from text values in a data source. Example, “Full Name” from First Name and Last Name fields. | ||
| Functions: Select the function to apply to the calculation. Date– The following functions are for use with Date fields. Date Add: Adds the specified number of days, months, or years to a date. Day: Return the day from the selected date field. Month: Return the month (1-12) from the selected date field. Number of Days: Return the number of days between two dates. Year: Return the year from the selected date field. Lookup and Reference – The following functions are for use with Lookup and Reference fields. Count: Count the number of people within a range of boxes. Level: Calculate the absolute level of a box. Levels Below: Calculate the number of levels that exist below a box relative to the box’s level. Occurrences: Calculate how often an individual or position occurs within a range of boxes. Reports To: Return the argument of the box’s manager Number – The following functions are for use with number fields. Average: Compute an average of the selected number field for all specified boxes. Calculate: Return the arithmetic computation (add, subtract, multiply, divide) of the selected field within a range of boxes. Max: Determine the maximum value of the selected field across all boxes. Min: Determine the minimum value of the selected field across all boxes. Total: Add all the selected field within a range of boxes. Text – The following functions are for use with text fields. Concatenate: Joins several text strings and/or fields into one text string. Left: Return the specified number of characters from the start of a text string. Length: Return the total number of characters in a text string. Lower: Convert all letters in a text string to lowercase. Mid: Return the characters from the middle of a text string, given a starting position and a length. Right: Return a specified number of characters from the end of a text string. Trim: Remove all spaces from a text string except for single spaces between words. Upper: Convert all letters in a text string to uppercase. | ||
| Formula Creation: Based on what Category- Function you choose, you are to begin creating the argument to support the formula. This may be a series of field drop down selections. | ||
| Formula Condition: Apply this function if you would like to only apply the formula to certain employees who meet specific conditions, such as full time or part time. (1) Check the box to allow the condition (2) Create your condition by creating rules Select the field that will decide the condition Select the qualifying argument (equal to, is, is not..) Then complete the qualifying condition Example, “Employee Status” is “Full Time” (3) Click Okay when complete | ||
| Formula Operations: In this section you can change the defaults for rounding, including/excluding assistants and matrixed employees. |
Once you have your formula created, press Save.
Your new calculated field is available to use in your views and conditional formatting.