Skip to main content

 

Planview Customer Success Center

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 PPM Pro 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-ppmpro@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 background 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. Other supported colors include "aqua", "cyan", "blue", "yellow", "orange", and "amber".

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

Entity: Task

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 application maintenance renewal date by month

Entity: Portfolio

Data Type: String

Display Type:

String

IfCase(Month([Portfolio].[Maintenance Renewal Date]),
1, '01 - January',
2, '02 - February',
3, '03 - March',
4, '04 - April',
5, '05 - May',
6, '06 - June',
7, '07 - July',
8, '08 - August',
9, '09 - September',
10, '10 - October',
11, '11 - November',
12, '12 - December',
''
)
Here the "Maintenance Renewal Date" is a user-defined date field on an application portfolio's details, but any date field could be used in the formula to derive a month. The numbers in front of the text month ensure that they appear in calendar order when used in reporting or for sorting purposes.
6 Display the Year and Quarter for project Start Date

Entity: Project

Date Type: String

Display Type: String

Ifcase (Month([Project].[Start Date]),

     1, concatenate(Year([Project].[Start Date]), " Q1"),

     2, concatenate(Year([Project].[Start Date] )+1, " Q1"),

     3, concatenate(Year([Project].[Start Date])+1, " Q1"),

     4, concatenate(Year([Project].[Start Date])+1, " Q2"),

     5, concatenate(Year([Project].[Start Date])+1, " Q2"),

     6,  concatenate(Year([Project].[Start Date])+1, " Q2"),

     7, concatenate(Year([Project].[Start Date])+1, " Q3"),

     8, concatenate(Year([Project].[Start Date])+1, " Q3"),

     9, concatenate(Year([Project].[Start Date])+1, " Q3"),

     10,concatenate(Year([Project].[Start Date])+1, " Q4"),

     11,concatenate(Year([Project].[Start Date])+1, " Q4"),

     12, concatenate(Year([Project].[Start Date])+1, " Q4"),

    "")

This will show the year and quarter of a project's start date. For example, if a project's start date is May 1, 2021, the value "2021 Q2" would be returned. You can replace Start Date with any project date, and likewise create for other entities. You can also change quarters as appropriate, for example, if your fiscal Q1 begins in July, change the 7 row to " Q1" instead of " Q3" and so on.

7 Display the difference between demand and allocated hours for a project

Entity: Project

Date Type: String

Display Type: String

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.
8 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]  
9 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.
10 Indicate if a task number field value (such as actual hours) is within a certain range (such as 50 and 100)

Entity: Task

Data Type: String

Display Type: String

If(
(([Task].[Actual Hours] >= 50) AND ([Task].[Actual Hours] <= 100)),
"Between 50 and 100",
"Outside of range")
This is for the Task entity. Similar formulas can be used for Project and other entities.
11 Display money/number fields without decimal places

Data Type: String

Display Type: String

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.
12 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.
13 Display Task Duration in days

Entity: Task

Data Type: Float

Display Type: Float

IfCase([Task].[Duration Format],

"d", Substring([Task].[Duration], 0, Find(" days", [Task].[Duration])),

"h", Round(ParseFloat(Substring([Task].[Duration], 0, Find(" hrs", [Task].[Duration]))) / 24, 2),

"m", Round(ParseFloat(Substring([Task].[Duration], 0, Find(" mins", [Task].[Duration]))) / 60 / 24, 2),

"w", Round(ParseFloat(Substring([Task].[Duration], 0, Find(" weeks", [Task].[Duration]))) * 5, 2),

0)

This assumes you want to use 5-day weeks as the standard, rounded to 2 decimal places for all values.
14 Detect if a Task has notes (useful for filtering)

Entity: Task

Data Type: String

Display Type: String

Filter Visibility: Yes

If(isNull([Task].[Latest Note Date]), 'No', 'Yes'))

Title the calculated field something like, "Task has Notes". Then you can use this in your task filters, such as "Task: Task has Notes is Yes" to filter your Tasks grid or report results to only display tasks that have notes.

You can vary the Entity as needed to have a similar calculated field for other entities such as portfolios, project logs, and so on. If you select an Entity of "Project" when creating the field, you can also use "Latest Comment Date" instead of "Latest Note Date" if you want to be able to filter on whether or not a project has status comments.

15 Detect if a project's latest status comment (or similarly note) is older than two weeks (useful for filtering)

Entity: Project

Data Type: String

Display Type: String

Filter Visibility: Yes

If( 
     (isNull ([Project].[Latest Comment Date]) )
     OR
     ([Project].[Latest Comment Date] < (Now() - 14)),
     'Yes', 
     'No'
)

Title the calculated field something like, "Latest Comment Date Older than 2 Weeks". Then you can use this in your project filters, such as "Project: Latest Comment Date Older than 2 Weeks is Yes" to filter your Projects grid or report results to only display projects that don't have a status comment within the past 2 weeks, including projects that don't have any status comments.

You can change the amount of time from 2 weeks to anything else by replacing "14" with the number of desired days in the past given the current date (the current date is represented by "Now()").

You also can use "Latest Note Date" instead if you want to be able to filter on notes for projects or any other entity (just select the relevant entity when creating the calculated field).

16 Determine the number of days since the last status comment was added to a project, returning a value of -1 if there have been no status comments on a project

Entity: Project

Data Type: Integer

Display Type: Inter

Filter Visibility: Yes

If(

     isNull([Project].[Latest Comment Date]),

     '-1',

     CalendarDays([Project].[Latest Comment Date],today())

)

Title the calculated field something like, "Number of Days since Last Status Comment". Then you can use this in your project filters, such as "Project: Number of Days since Last Status Comment is greater than 14" to filter your Projects grid or report results to only display projects that have status comments older than 2 weeks. If you wanted to identify projects that don't have any status comments, use the filter "Project: Number of Days since Last Status Comment is -1". Or, if you wanted to make sure you could filter such that projects with status comments older than 2 weeks and projects with no status comments are both returned, change the "-1" in the formula to a really large (and unlikely) number such as "1000000".

You also can use "Latest Note Date" instead if you want to be able to filter on notes for projects or any other entity (just select the relevant entity when creating the calculated field).

17 Determine an application's TIME lifecycle based on business value and EA scores

Entity: Portfolio

Data Type: String

Display Type: String

If(
     (([Portfolio].[Business Value Score]<=5) and ([Portfolio].[Business Value Score]>0) and ([Portfolio].[Enterprise Architecture Score]>5) and ([Portfolio].[Enterprise Architecture Score]<10)), 'Tolerate', 
          If(
               (([Portfolio].[Business Value Score]>5) and ([Portfolio].[Business Value Score]<10) and ([Portfolio].[Enterprise Architecture Score]>5) and ([Portfolio].[Enterprise Architecture Score]<10)), 'Invest', 
                    If(
                         (([Portfolio].[Business Value Score]>5) and ([Portfolio].[Business Value Score]<10) and ([Portfolio].[Enterprise Architecture Score]<=5) and ([Portfolio].[Enterprise Architecture Score]>0)), 'Migrate', 
                              If(
                                  ( ([Portfolio].[Business Value Score]<=5) and ([Portfolio].[Enterprise Architecture Score]<=5) and ([Portfolio].[Enterprise Architecture Score]>0)), 'Eliminate', 'Undefined'
                              )
                    )
          )
)

TIME = Tolerate, Invest, Migrate, Eliminate, and is useful in analyzing an application's lifecycle and management.

This calculated field relies on two portfolio user defined numeric fields that capture scores for business value (Business Value Score) and for enterprise architecture value (Enterprise Architecture Score). 

18 Display Red, Green, Yellow text values on top of relevant colors when the Task Overdue HTC is a certain value

Entity: Task

Data Type: String

Display Type: String

If([Task].[Overdue HTC] <5, "Green",
If([Task].[Overdue HTC] <=9, "Yellow",
If([Task].[Overdue HTC] >=10, "Red", "Null")))

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. Other supported colors include "aqua", "cyan", "blue", "orange", and "amber".
19 Display a different colored background with Bold Font White or Bold Black Font Example, for various list values

Entity: <whichever the field list belongs to, such as Project>

Data Type: String

Display Type: HTML

IfCase([Project].[RYG Overall],

"Red",     '<div style="background-color:red;color:white;font-weight:bold">Red</div> ',

"Green",  '<div style="background-color:green;color:white;font-weight:bold">Green</div> ',

"Yellow",  '<div style="background-color:yellow;color:black;font-weight:bold">Yellow</div> ',"")

This example changes the background color for a project "RYG Overall" lookup list field. You can try other fields and colors as needed, take care with what text color you choose given the background color as some colors do not work well together (for example, White font on Yellow does not display well).

Picture1.png

Picture2.png

Picture3.png

20 Display either a red, yellow, or green square when a project's Actual Cost is greater than, in between, or less than specific values

Entity: Project

Data Type: String

Display Type: HTML

If([Project].[Actual Cost (AC)]>100000,concatenate('<div style="background-color:red; height:20px; width: 20px">','</div>'),
If([Project].[Actual Cost (AC)]>50000,concatenate('<div style="background-color:yellow; height:20px; width: 20px">','</div>'),
concatenate('<div style="background-color:green; height:20px; width: 20px">','</div>')))

You can change the numeric values (and even the relevant project field) as needed. For example, if the project's Actual Cost is less than or equal to 50,000, a 20x20 pixel green square would be displayed:

green square.png

21 Display the total scheduled cost for a Project

Entity: Project

Data Type: Float

Display Type: Money

GetChildEntityData("Sum", [Task].[Scheduled Cost], 'Summary Task')

This is example is for Scheduled Cost. Other task cost money fields could be substituted to create other total project cost fields. Here 'Summary Task' is the name of a pre-built filter.

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).

22 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).

23 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).
24 Find out how many staffing hours do I have left on a project, from the relative day today to the end of the allocations

Entity: Project

Data Type: Float

Display Type: Float

GetStaffingData([Allocated Role].[Total Allocated Hours], Today(), GetChildEntityDate('Max', [Allocated Role].[End Date]))  
25 Find out how much remaining demand cost do I have left on a project, from the relative day today to the end of the allocations

Entity: Project

Data Type: Float

Display Type: Money

GetStaffingData([Allocated Role].[Total Allocated Cost], Today(), GetChildEntityDate('Max', [Allocated Role].[End Date]))  
26 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).
27 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).
28 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).

29 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).

30 Display a text value in a Financial Entry report column instead of an "X" to represent capitalized and blank/no value when not capitalized

Entity: Financial Entry

Data Type: String

Display Type: String

If([Financial Entry].[Capitalized],'Capex','Opex') This formula can be used to replace the "X" value representing when a financial entry is capitalized with another text value, such as "Capex", and replacing the blank/no value representing when a financial entry is not capitalized with a text value, such as "Opex".
31 Simple Financial Entry Capitalized/Non-Capitalized Forecasts/Actuals

Entity: Project

Data Type: Float

Display Type: Money

Formula for Capitalized Forecasts:

abs(GetChildEntityData("Sum", [Financial Entry].[Forecast Amount (Signed)], 'Capitalized'))

Formula for Capitalized Actuals:

abs(GetChildEntityData("Sum", [Financial Entry].[Actuals Amount], 'Capitalized'))

Formula for Non-Capitalized Forecasts:

abs(GetChildEntityData("Sum", [Financial Entry].[Forecast Amount (Signed)], 'Noncap'))

Formula for Non-Capitalized Actuals:

abs(GetChildEntityData("Sum", [Financial Entry].[Actuals Amount], 'Noncap'))

NOTE: In these formulas the last variable is for a specific filter

  •  'Capitalized' is a financial entry filter to identify only capitalized financial entries  Screen Shot 2021-02-13 at 12.43.16 PM.png
  • 'Noncap' is a financial entry filter to identify only non-capitalized financial entries Screen Shot 2021-02-13 at 12.43.06 PM.png
32 Simple Financial Entry estimate at completion

Entity Type: Reporting Entity

Category: Finance

Entity: Financial Planning

Data Type: Float

Display Type: Money

If ([Financial Planning].[Forecast Amount]>ABS([Financial Planning].[Total Child Actuals Amount]), [Financial Planning].[Forecast Amount], ([Financial Planning].[Total Child Actuals Amount]))

Use for simple calculations of estimate at completion (EAC) for your Financial Entries, only for Cash Outflow (expense) entries.

NOTE: Only works with respect to Legacy reports, not new reports beta

33 Simple Financial Entry estimate to complete

Entity Type: Reporting Entity

Category: Finance

Entity: Financial Planning

Data Type: Float

Display Type: Money

If ([Financial Planning].[Forecast Amount]>ABS([Financial Planning].[Total Child Actuals Amount]),[Financial Planning].[Variance],0)

Use for simple calculations of estimate to complete (ETC) for your Financial Entries, only for Cash Outflow (expense) entries.

NOTE: Only works with respect to Legacy reports, not new reports beta

34 Calculation based on the values of 1 or more lookup list selections, such as Weighted Shortest Job First (WSJF), for example for a project WSJF

Two types of calculated fields:

1. Lookup list numeric value.

Entity: Project

Data Type: Integer

Display Type: Integer

2. Calculation such as WSJF:

Entity: Project

Data Type: Float

Display Type: Float

1. For converting a lookup list option selection to a numeric value, such as for a lookup list entitled 'Effort' that has 5 options (make sure the lookup list option titles match exactly within the calculated field):

IfCase([Project].[Effort],

     "Less than 2 days", 1,

     "Greater than 2 days and less than 2 weeks", 3,

     "Greater than 2 weeks and less than 1 month", 5,

     "Greater than 1 month and less than 3 months", 8,

     "More than 3 months", 13,

     "")

2. For calculating WSJF based on the 4 lookup list selection values:

([Project].[Business Value Value]+[Project].[Time Criticality Value]+[Project].[Risk Reduction Opportunity Value])/[Project].[Effort Value]

Setup lookup lists with meaningfully worded options, for example for WJSF you would have lookup lists for Effort, Business Value, Time Criticality, and Risk Reduction Opportunity. Add these to relevant entity details.

Creating calculated fields to convert a lookup list's option selections to numeric values.

Create a calculated field for WSJF using those calculated fields. Add this field to relevant entity details and reports, with appropriate restrictions/visibility based on who you want to be able to see the values.

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).
  • Calculated field displays ok in reports and/or validates/tests successfully, but does not show up on Details correctly - Generally, any errors in displaying the results of a calculated field (including _not_ displaying a value) result from either misconfiguration of the DataType/DisplayType or not being correctly added to the Details for the corresponding Category. Double-check that the value returned matches the Data and Display type. For example, if a calculated field is defined as “Data Type” = Float / “Display Type” = Money, but returns a value enclosed with HTML tags then the field will fail to display correctly on Details; when the formula is tested, it returns an HTML string and displays that in the output if the Formula Editor, but when the UI renders it, it tries to render as Money and fails.
  • 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 Legacy or new beta 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 Legacy 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 PPM Pro 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.