Formula symbols explained
You insert symbols into formulae using the Symbol button on the Formula Properties dialog. A menu appears, from which you can choose the symbol that you want to insert into the formula.
Arithmetic symbols
You insert these symbols into formulae to carry out mathematical functions on two fields or values.
+
| Description | Adds two values together. |
| Example |
Cost(30) + Cost(1080)
Sums the costs related to the cost centre with an object ID of 30 and the costs related to the cost centre with an object ID of 1080. |
-
| Description | Subtracts the second value from the first. |
| Example |
Start - '1w'
Subtracts one week from the start date of an object. |
| Notes | As well as being used to subtract one value from another, the - symbol can also be used to represent negative values, for example -1; -'£30', '-£30', '£-30'; -'2d 3h', '-2d 3h'. Note the various positions in which the - symbol can appear, all of which are valid. |
*
| Description | Multiplies two values together. |
| Example |
Cost * 0.2
Multiplies costs by 0.2, effectively returning the amount of tax involved in a cost where the tax rate is 20%. |
/
| Description | Divides the first value by the second. |
| Example |
Cost / 20
Divides costs by 20, effectively returning the amount of tax involved in a cost where the tax rate is 20%. |
Comparison symbols
You insert these symbols into conditional formulae to compare two fields or values.
=
| Description | Tests whether two values are equal. |
| Example |
If(Task.Name = "Roofing", High, None)
A traffic light indicator that displays high priority for all tasks called "Roofing", and no priority for all other tasks. |
<>
| Description | Tests whether two values are different. |
| Example |
If(TaskCodeLibrary(4173) <> "2 Bed Terraced", None, High)
A traffic light indicator that displays high priority for all tasks to which the "2 Bed Terraced" code has been assigned from the code library that has an object ID of 4173, and no priority for all other tasks. |
>
| Description | Tests whether the first value is greater than the second. |
| Example |
If(Search("issue", Notes) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue", and no priority for all other tasks. |
>=
| Description | Tests whether the first value is greater than or equal to the second. |
| Example |
If([~CB].Income >= Income, High, Low)
A traffic light indicator that displays high priority for all tasks with projected income in the current baseline that is greater than or equal to the income in the live data, and low priority for all other tasks. |
<
| Description | Tests whether the first value is less than the second. |
| Example |
Try(Switch(Income < Cost, High), Low)
A traffic light indicator that displays high priority for all tasks with costs that exceed income, and low priority for all other tasks. |
<=
| Description | Tests whether the first value is less than or equal to the second. |
| Example |
Try(Switch(Income <= [~PB].Income, High), Low)
A traffic light indicator that displays high priority for all tasks with income in the live data that is less than or equal to projected income in the project baseline, and low priority for all other tasks. |
Logic symbols
You insert these symbols into conditional formulae to specify the condition, or conditions, that must be met.
AND
| Description | Joins two conditions together and indicates that both conditions must be met. |
| Example |
If((Search("issue", Notes) > 0) AND (TaskCodeLibrary(4173) = "2 Bed Terraced"), High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue" and to which the "2 Bed Terraced" code has been assigned from the code library that has an object ID of 4173, and no priority for all other tasks. |
OR
| Description | Joins two conditions together and indicates that either condition must be met. |
| Example |
If((Search("issue", Notes) > 0) OR ([~CB].Income >= Income), High, Low)
A traffic light indicator that displays high priority for all tasks that either have notes that contain the word "issue", or with projected income in the current baseline that is greater than or equal to the income in the live data, and low priority for all other tasks. |
NOT
| Description | Tests whether a condition is not met. |
| Example |
If((Search("issue", Notes) > 0) AND NOT (TaskCodeLibrary(4173) = "2 Bed Terraced"), High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue" and to which the "2 Bed Terraced" code has not been assigned from the code library that has an object ID of 4173, and no priority for all other tasks. |
Miscellaneous symbols
These are additional characters that can be used in formulae.
()
| Description | Opening and closing parenthesis, used (always in a pair) to specify the order in which expressions within formulae are evaluated. Enclosing an inner expression within parentheses ensures that it is completely evaluated before the outer expression. |
| Example |
If(Search("issue", Notes) > 0, High, None)
A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue", and no priority for all other tasks. |
| Notes |
In the absence of any parentheses, calculations are carried out in the following order of precedence:
For example, the following formula produces a result of 11 rather than 21 because the multiplication is carried out before the addition:
5+2*3
If parentheses were added to this formula, changing it to (5+2)*3, it would produce a result of 21 rather than 11. This is because the addition of parentheses to the formula means that the expression that is contained within the parentheses is evaluated before the rest of the formula, overriding the normal order of precedence. |
&
| Description | Concatenates two strings. |
| Example |
Cost / UDF("CostPerDayFactor") & " Days"
The cost of a task divided by a user-defined field that contains the cost per day, with the text string " Days" appended to the resulting value. |
Variable dates
All of the variable dates that are available for use in Asta Powerproject, for example Today, Tomorrow, Month Start and Year End, can be used within formulae. There are many uses of variable dates within formulae, some examples of which are displayed below. Note that you must remove all spaces from the names of variable dates when using them in a formula.
| Example 1 |
Task.Start - Today
The number of days from today until the start date of a task. |
| Example 2 |
Try(Switch
A traffic light indicator that displays the urgency of orders. In this example, "OrderPlaced" and "DeliveryTimeInDays" are user-defined fields that store data at bar level; "OrderPlaced" is a date-type field and "DeliveryTimeInDays" is a float-type field. |
Note that statements such as Today + '1.5d' or Tomorrow - '2w' are supported within formulae, provided that you enclose the duration constants within single quotation marks, as illustrated above.
Enumerated values
The results of several spreadsheet fields can differ depending on which language version of Asta Powerproject you are using. For example, the possible results of the Priority field in an English version of Asta Powerproject are None, Low, Medium and High; in a German version of Asta Powerproject the results of this field would be Keine Priorität, Niedrig, Normal and Hoch.
If you were to refer to the results of these fields in formulae by enclosing the text of the results in double quotation marks (for example, if you were to refer to the results of the Priority field as "High" or "Low"), your formula would not work in a foreign language version of Asta Powerproject. To get around this problem, you should refer to the results of these fields using 'enumerated values'. Each possible result of the affected fields has a corresponding enumerated value - which in many cases, is the same as the English version of the results. For example, the Waiting State field gives results that are normally displayed in the spreadsheet as Waiting and Can Start. The enumerated values that correspond to these results are Waiting and CanStart. In addition to this, enumerated values can be used as parameters or return values from a number of functions.
Two uses of enumerated values are displayed below:
| Example 1 |
If((Constraint = StartOn), High, None)
A traffic light indicator that displays high priority for all tasks to which a Start On constraint flag has been applied, and no priority for all other tasks. |
| Example 2 |
If((Search("ground", Task.Name) > 0) AND (ProcessState = NotStarted), High, None)
A traffic light indicator that displays high priority for all tasks that contain the word "ground" in their name and that have not yet started, and no priority for all other tasks. |
The following tables display the various enumerated values that are available:
Enumerated values for the Priority field
| Priority field results | Corresponding enumerated values |
| None | None |
| Low | Low |
| Medium | Medium |
| High | High |
Enumerated values for the Constraint field
| Constraint field results | Corresponding enumerated values |
| None | None |
| Start On | StartOn |
| Start On Or Before | StartOnOrBefore |
| Start On Or After | StartOnOrAfter |
| Finish On | FinishOn |
| Finish On Or Before | FinishOnOrBefore |
| Finish On Or After | FinishOnOrAfter |
| Work Between | WorkBetween |
| Deadline | Deadline |
Enumerated values for the Placement field
| Placement field results | Corresponding enumerated values |
| ASAP | ASAP |
| ALAP | ALAP |
| ASAP Force Critical | ASAPForceCritical |
Enumerated values for the Process State field
| Process State field results | Corresponding enumerated values |
| Not Started | NotStarted |
| In Progress | InProgress |
| Completed | Completed |
Enumerated values for the Waiting State field
| Waiting State field results | Corresponding enumerated values |
| Waiting | Waiting |
| Can Start | CanStart |
Enumerated values for the Type field
| Type field results | Corresponding enumerated values |
| Bar | BarType |
| Task | TaskType |
| Milestone | MilestoneType |
| Expanded Task | ExpandedTaskType |
| Hammock Task | HammockTaskType |
| Permanent Scheduled Allocation | PermanentScheduledAllocationType |
| Cost Allocation | CostAllocationType |
| Consumable Scheduled Allocation | ConsumableScheduledAllocationType |
| Permanent Resource | PermanentResourceType |
| Perm Resource Skill | PermResourceSkillType |
| Cost Centre | CostCentreType |
| Consumable Resource | ConsumableResourceType |
| Permanent Demand Allocation | PermanentDemandAllocationType |
| Consumable Demand Allocation | ConsumableDemandAllocationType |
| Task Completed Section | TaskCompletedSectionType |
| Allocation Completed Section | AllocationCompletedSectionType |
Enumerated values for the Subtype field
| Subtype field results | Corresponding enumerated values |
| Scheduled | ScheduledType |
| Demand | DemandType |
| Pending Demand | PendingDemandType |
| Cost | CostType |
| Income | IncomeType |
| Summary | SummaryType |
| Chart | ChartType |
| Start | StartType |
| Finish | FinishType |
Enumerated values for the ContextType function
The enumerated values in the following table do not relate to a spreadsheet field; they relate to the results of a function - called ContextType - that you can use in formulae to ascertain the type of object.
More information on the ContextType function
| ContextType function results | Corresponding enumerated values |
| Allocation | AllocationContext |
| Task | TaskContext |
| Bar | BarContext |
| Sort Band | SortBandContext |
Baseline reference symbols
You insert these symbols into formulae to make reference to values from a baseline rather than from the live data.
PB
| Description | Refers to the project baseline. |
| Example |
Try(Switch(Income <= [~PB].Income, High), Low)
A traffic light indicator that displays high priority for all tasks with income in the live data that is less than or equal to projected income in the project baseline, and low priority for all other tasks. |
CB
| Description | Refers to the current baseline. |
| Example |
(Actualcost / [~CB].Cost) * 100 & "%"
Shows the actual costs as a percentage of the costs that were planned in the current baseline. |
LP
| Description | Refers to the live data; it is not usually necessary to use this symbol, as formulae refer to the live data by default unless you specify otherwise by referring to a baseline. |
| Example |
If((Search("issue", Notes) > 0) OR ([~CB].Cost <= [~LP].Cost), High, Low)
A traffic light indicator that displays high priority for all tasks that either have notes that contain the word "issue", or with projected costs in the current baseline that is less than or equal to the costs in the live data, and low priority for all other tasks. |