Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Using Formulas

Objective

After completing this lesson, you will be able to create a new Formula field for reporting.

Formula Fields

the analytics menu options in the side menu, including the Formulas option, which you would select to review an existing formula or create a new one

There are thousands of reportable fields available within SAP Fieldglass Analytics, many of which satisfy the reporting needs of organizations that use the product. However, there are times when a unique data field is required that does not already exist, and this is where Formulas come in.

The Formula section of the Analytics menu allows you to create new fields based on the currently existing data points and applying certain formulas to them. Formula fields can be created to do many things, such as performing calculations on numeric fields, finding the time difference between dates, or concatenating multiple text fields together.

Create a Formula Field

Brian, the manager of the worker procurement program for WorkingNet Networking Inc., a manufacturer of data networking equipment, is interested in tracking the number of days each Job Posting is available to suppliers before a hire is made. However, he doesn’t want any time the posting may have been on hold to be factored in. Instead of making a bunch of manual calculations in the raw data itself, Brian decides to create a Formula field to use within his reporting.

As the SAP Fieldglass administrator for WorkingNet, you’re the person tasked with creating the formula field for Brian.

ExerciseStart Exercise

Example Formulas & Use Cases

The Formula functionality uses a combination of Excel and SQL. Once a function has been selected, you can design the formula using Excel language, but all formulas will be parsed from Excel to SQL on the back-end.

Additionally, the Formula functionality is only based on report columns, meaning data on two different rows of a report cannot be manipulated, so sum and averaging data is limited, and order of operations must be followed.

Example A:

This Formula provides the actual name of the day of the week time was entered on a time sheet:

DATENAME(weekday,[Time Sheet | Line Items | Time Entry Date])

Screenshot of the Create a Formula page, with the formula field enhanced to show the DATENAME function from Example A

Example B:

This Formula buckets new hires based on their current tenure, grouping them into the last 30 days, 31 to 60 days, and 60 plus days:

Screenshot of the Create a Formula page, with the formula field enhanced to show the 3 variables, tabulated and color coded, from Example B.

Example C:

This Formula tags time sheets as one of three categories:

If a time sheet is in Draft status, AND it is less than 7 days old, identify it as "Exclude."
If a time sheet is in Draft status, then identify it as "Include."
If a time sheet is not in Draft status, then identify it as "Not Draft."

Screenshot of the Create a Formula page, with the formula field enhanced to show the 3 criteria and variables, tabulated and color coded, from Example C.

Next lesson