Derived Fields: The Basics
- Josh Daugherty (Deactivated)
Analytics
Applying the right combinations of summary functions and groupings can unlock virtually limitless possibilities for processing and presenting data.
Another way to leverage Sharpen’s powerful Insights engine is by creating derived fields.
Derived fields can take existing fields, summary functions, and groupings within the reporting database and turn them into new fields that can be used across all of your reports.
Think of derived fields as a way to extend and transform those existing fields into more meaningful information, while also saving time. A common use is to create them to line up with specific KPIs.
The value of derived fields extends beyond the original creator and/or use case, too, as derived fields, once constructed, are available across the organization. They are accessible the same way any standard field in the data source is.
Derived fields work like standard fields in many ways, but there are a few notable differences to note, as you cannot add a summary function to, filter by, or group by a derived field.
Once created, a derived field becomes part of the data source it was created in—meaning others within the organization can see it (and use it in their own reports if they so choose).
Derived Fields Manager
From within Insights, the Derived Fields Manager is accessible via the sidebar menu.
On clicking the Derived Fields Manager icon, we're taken to the Derived Fields Manager interface (shown below).
Creating a New Derived Field
Creating a new derived field begins wtih opening the Derived Fields Manager, as described above.
From there, the first step is to select the appropriate data source from the drop-down that appears. In most cases (including this one), Queue Segment Activity will be the best choice.
On selecting the data source, a second drop-down will appear. This is where we could select an existing derived field to edit.
In order to build a brand new derived field, though, instead click the + New Derived Field button in the upper-right corner:
On clicking the + New Derived Field button, the rest of the field creation interface will load. This is where we'll be able to construct a new field by piecing together SQL building blocks.
From here, the process of building a derived field primarily involves using basic SQL building blocks to create custom functions that can be used throughout Insights. By enabling a "building blocks" approach, the Derived Fields Manager makes creating custom fields much more accessible than you may expect—even with limited SQL know-how.
Basic SQL building blocks in the derived fields manager, shown in the toolbar below, include:
add group: For more complex derived fields, adding a group helps keep things orderly and impacts the order of operations (like in math). This is especially useful if you need to combine multiple fields, and then apply an operator to the resulting value.
add fields: Click this button to add in a “field” building-block, from which you can select (via drop-down) the field you want to use.
mathematical operators (+), (-), (/), and (*): Clicking any of these four buttons inserts its respective operator (addition, subtraction, division, multiplication) into the SQL/field.
additional operators (=), (<), (>), (>=), (<=), (<>), (!=), (!<), (!>): Clicking any of these buttons inserts its respective operator (equals, less than, greater than, greater than or equal to, less than or equal to, greater than or less than, not equal to, not less than, not greater than).
integer input: This button inserts an integer field, which can be populated with a specific numerical value.
NOW(): This button automatically retrieves/returns the current system date and time.
separator: Apply separators to derived field formulas utilizing the CONCAT function.
clear button (not shown): Use this button to clear your progress and start over.