# Calculated Fields Cookbook

Below are examples of calculated fields that customers and our Services team have created to support various needs - kind of a cookbook of calculated fields. Many of these can be extrapolated or adapted to perform similar calculations for other fields. Most functions used in calculated fields are similar to functions available in other applications, such as Excel; if you have a grasp of Excel functions and what they allow, you’ll have a leg up on Innotas calculated fields. The Help topic on Functions also has specific additional examples.

Please do contribute calculated fields you have created that may be of use to others by providing the details in an email to productmanagement-innotas@planview.com.

#### Examples

# How Do I.... Data Type/Display Type Formula Notes
1 Determine and display if a project is over budget or under budget

Entity: Project

Data Type: String

Display Type: String

If([Project].[Actual Cost]<=[Project].[Material Expense Estimate], "On Budget", "Over Budget") Displays "On Budget" when total project actuals are less than the estimated material expenses, else displays "Over Budget" (adjust text to display as needed).
2 Determine and display if a project is late or on time

Entity: Project

Data Type: String

Display Type: String

If([Project].[Target Date]<[Project].[Completion Date], "Project is Late", "Project is On Time")

or

If(([Project].[Target Date]<Today()) and ([Project].[Status]="Open"), "Red", "Green")

The first formula just displays "Project is Late" when project target date is earlier than the project completion date, else displays "Project is On Time" (adjust text to display as needed).

The second formula displays "Red" with a red backgroudn color if the project target date is before the current date and the project is still open, therefore late, and "Green" with a green background color if not.

3 Display the number of week days between a task's Target and Complete Dates

Data Type: String

Display Type: String

WeekDays([Task].[Complete Date],[Task].[Target Date])-1 This is for the Task entity. Similar formulas can be used for two Project dates.
4 Display the number of work days left in a project

Entity: Project

Data Type: Float

Display Type: Float

[Project].[Duration (Work Days)]-[Project].[Elapsed (Work Days)]

This is for work days only. For other types of time differences (days, weeks, months, quarters), use the relevant Duration and Elapsed field with those labels.
5 Display the difference between demand and allocated hours for a project

Entity: Project

Date Type: Float

Display Type: Float

FormatNumber(([Project].[Demand Hours]-[Project].[Actual Hours]), '#,##0.0') Here the FormatNumber function displays the difference with one decimal place. Other number formats are possible, refer to FormatNumber for details. Removing the FormatNumber function items and just using [Project].[Demand Hours]-[Project].[Actual Hours] in the formula editor will display up to 12 decimal places.
6 Display the difference between labor forecast and actual hours for a project

Entity: Project

Data Type: Float

Display Type: Float

[Project].[Actual Hours]-[Project].[Estimated Hours]
7 Only display the first 50 characters of a project's Description followed by "..."

Entity: Project

Data Type: String

Display Type: String

If (Length([Project].[Description]) < 50, [Project].[Description], Concatenate(Left([Project].[Description], 50),"..."))

This displays the full project Description if it is less than 50 characters, else it will display the first 50 characters of the project Description followed by "...". Similar formulas can be used for other entities and text fields, with different numbers of characters.
8 Indicate if a task number field value (such as actual hours) is within a certain range (such as 50 and 100)

Data Type: Float

Display Type: Float

If(
"Between 50 and 100",
"Outside of range")
This is for the Task entity. Similar formulas can be used for Project and other entities.
9 Display money/number fields without decimal places

Data Type: Float

Display Type: Money/Float

FormatNumber(field name, '#,##0') Here, field name is the relevant money/number field, e.g., for a project's Actual Cost, FormatNumber([Project].[Actual Cost], '#,##0'). Other money/number formats are possible, refer to FormatNumber for details.
10 Display a date in a particular format

Data Type: String

Display Type: String

FormatDate([Project].[Start Date], 'yyyy-MM') In this example for a Project entity, the Start Date will be displayed with year first, e.g., for sorting by year, 2017-02 for February 2017. Other date formats are possible, refer to FormatDate for details.
11 Display Task Duration in days

Data Type: Float

Display Type: Float

0)

This assumes you want to use 5-day weeks as the standard, rounded to 2 decimal places for all values.
12 Display Red, Green, Yellow text values on top of relevant colors when the Task Overdue HTC is a certain value

Data Type: String

Display Type: String

This example looks at the value of Overdue HTC (hours to complete) and if the field value is less than 5, will display the text "Green" on top of a green background. Likewise "Yellow" is displayed on top of a yellow background when the value is between 4 and 10, and "Red" if the value is greater than or equal to 10. Other entities besides Task and numeric fields besides Overdue HTC can be used.

Data Type: String

Display Type: HTML

Here URL should be replaced with the desired link, being sure to place it between single quotes. The Link Display Name should be the text you want to appear in lieu of the URL. For example: Concatenate("<a href='http://www.planview.com/'>Planview</a>") would display a link to Planview's website.

NOTE: Ensure that Display Type is set to HTML, else it will not render correctly.

14 Display the total scheduled cost for a Project

Entity: Project

Data Type: Float

Display Type: Money

This is example is for Scheduled Cost. Other task cost money fields could be substituted to create other total project cost fields.

NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).

15 Display the total estimated cost for a Portfolio

Entity: Portfolio

Data Type: Float

Display Type: Money

GetChildEntityData("Sum", [Project].[Estimated Cost (Total)])

This is example is for Estimated Cost (Total). Other project cost money fields could be substituted to create other total portfolio cost fields.

NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).

16 Find the latest date of allocations on a project

Entity: Project

Data Type: Date

Display Type: Date

GetChildEntityData("Max", [Allocated Role].[End Date]) NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).
17 Find the earliest Start Date on component project for a portfolio

Entity: Portfolio

Data Type: Date

Display Type: Date

GetChildEntityData("Min",[Project].[Start Date]) NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).
18 Find the latest Target Date on a component project for a portfolio

Entity: Portfolio

Data Type: Date

Display Type: Date

GetChildEntityData("Max",[Project].[Target Date]) NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).
19 Find the earliest date for allocations on a project

Entity: Project

Data Type: Date

Display Type: Date

GetChildEntityData("Min", [Allocated Role].[Start Date])

NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityData should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityData should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).

20 Display a count of all my open issues/project logs on a project

Entity: Project

Data Type: Integer

Display Type: Integer

GetChildEntityCount(6,1)

This formula uses IDs to identify the entity and relevant filter, where "6" represents Project Issue entity and "1" is a standard filter ID for All Open. Refer to the GetChildEntityCount help topic for more info on this function and related entity/filter IDs.

NOTE: USE WITH EXTREME CAUTION!!! GetChildEntityCount should be tested for performance impacts and used carefully. If there are a very large number of child entities, the function could cause the system to time out. For this reason, calculated fields that use GetChildEntityCount should NEVER be used as a grid column (unless you really want your grid to be slow to render for all users, all of the time).

#### Trouble-Shooting, Common Errors, and FAQs

• Missing end parentheses - For If and similar functions, the last clause must have enough parentheses to cover the number of starting parentheses for the preceding clauses. For example, in the "Display a specific text value instead of a % value" example above, there are 4 "(" due to the 3 If clauses and the last implicit Else clause, so it must have 4 ")" at the end of the formula.
• Cannot find fields within the Formula Editor/Select Fields tree - Most entities have "Child" folders and specific child entity folders that include relevant available fields. However, a few, such as the Portfolio calculated field entity, do not. This doesn't mean that its relevant child entity available fields cannot be used in functions such as GetChild..., you will just need to enter the relevant field title directly rather than selecting from an absent folder (and we'll see about adding these folders in the future).
• When do I use “Standard Entity” versus “Reporting Entity” when creating a calculated field? Use “Standard Entity” when you want the calculated field to be included within the selected entity's Available Fields, so it can be selected for reports, entity details, or grid columns (again, use extreme caution and avoid including a calculated field in grid columns that uses a GetChildEntity function as it may make the grid too slow to render). Use “Reporting” when you only want the calculated field to be available for use within the selected Category of reports for the selected Entity.
• Where do you obtain the Target ID displayed in the calculated fields Validate/test window? The ID is the entity ID. For example if calculated field is for a Project entity, copy the Project ID on the Project Details view.
• Where do you obtain the entity type ID used in GetChildEntity functions? Refer to the GetChildEntityCount help topic for details, specifically the "Entity Reference for all GetChildEntity functions" table.
• ­Does Innotas react differently with a single quote vs. a double quote within calculated fields? No, but best practice is to remain consistent. If you’re concatenating strings you can user double quotes outside and/or single quotes to include inside the string. For example, the following concatenations would work identically:

`Concatenate("Project Status", ' ', [Project].[Implied Status], ' ', 'as of', " ", [Project].[Last Modified Date])`

`Concatenate('Project Status', ' ', [Project].[Implied Status], ' ', 'as of', ' ', [Project].[Last Modified Date])`

• Don't see your example - Read through the Help topic on Functions and see if any of the examples or descriptions help you formulate the necessary calculated field.