The formula field can be added to your templates to perform mathematical and logic-based functions with your goal data. In this article, we'll give you our most commonly-used formulas for you to add as custom fields ("attributes") on your templates.
What attributes can be used in Formula Field?
Certain fields are always associated with your goals. For example, every goal in Cascade is always going to have an initial value, target value, a start/end date, today's progress, etc... You can use all of these to create a formula and to calculate specific things on each goal. Custom number fields are particularly effective.
Before we get started, one thing to keep in mind is that you can actually pull any custom fields (attributes) on any aligned templates. For example, if your Strategy Model has KPIs and Projects aligned to Objectives, you'd be able to pull the Objectives' fields onto your KPI's formula.
How to create a formula:
-
From a template, click the orange "Add Attribute" button on the top left and select the Formula Attribute.
-
Click on the attribute to open up its configuration.
-
Rename your formula (in the "Title" field).
-
Scroll down to "Add Attribute" and click on the first drop down. Select the template you'd like to pull your fields from. In this example, I'm picking Objectives.
5. Click on the second drop down and select the first field you'd like to include in your formula. In this example, I'm picking Due Date.
6. Now select your operator. In this example, I'm picking subtract "-". As you can see, the text field in the middle of your config bar (where it says Type below or use Operators & Attributes) will begin to populate with your formula.
7. Now I'm going to go back to my "Add attribute" section of the config (NOT the orange button) and select another attribute. In this example, I'm going to pick "Start Date"
8. You've now created a formula that gives you the total number of days expected to complete a project! Congrats! You can now go to your snapshots, and add this field as a column next to your objectives. The numbers will automatically be generated based on your existing Start and Due Dates.
9. The last step is you must decide how to display your results. In this specific example, we're only looking for the duration of the project in days, so we're looking for the outcome. This means we can keep "Display Type" as Outcome.
However, if you'd like to group those outcomes, you can change it to "Group Name". An example of when you'd use the groupings Display is if you're trying to make a formula using two number attributes, and having a numerical output wouldn't make sense.
For example, if you're trying to calculate the total for Impact of Risk ( "Likelihood X Severity"), it doesn't make sense for your formula to show you a result of "19". The result you're trying to display is not an integer, but rather a word to signify a certain range of numbers, for example 0-10 is low impact, 11-16 is medium impact, and so on. So in this case, you would open up "Add formula Grouping" as seen below:
You would then add your groups based on different ranges of numbers. For example, any result of "Likelihood X Severity" between 17 and 25 will give me a Risk score of "High."
How do Formula Field results display in Cascade?
Formula results can be displayed as an outcome or group names. An Outcome is simply the result of the formula expression.
You can also set a range of expression result as a group and display the name of the group in Cascade by clicking on 'Add Formula Grouping'. This is useful when calculating scores or status. You can also set text and background colour on your group names.
Use Case Examples
Risk Rating:
Likelihood and Severity are two number attributes. The numbers are grouped based on likelihood/severity, usually 1 -5 on a likert scale.
Goal Duration:
See above
Financial Status:
This formula is determining is the resources required are "No" and if you've gone over your costs versus budget.
RAG Status:
See below
Display Status for Heat Maps:
For detailed information on Status, scroll to bottom of page.
As you've probably noticed, "Status" is not one of the fields you can automatically access when building out a formula, but it's a useful field to use if you'd like to display anything on track, behind, or complete on our Heat Map Widget. For example, you can display all Project Sponsors on the Y Axis of a heatmap, and then use the formula below to display what is on track, complete, and behind:
How the Heat Map looks when you plot the Project Portfolio attribute on the X axis, and the Status Formula attribute on the Y Axis:
Using the "IF" Operator:
The formula attribute in Cascade also supports conditional operators. The conditional ‘if...else…’ statement in Formula attribute is written with the syntax of a question mark (?) followed by a colon (:) demonstrated below:
Condition statement ? Return value on true : Return value on false
In other words:
does a = b ? yes : no
The condition is always written first, followed by a question mark (?). The first value will return if the condition is true and the second value will return on false. For the example below, it checks whether the the student has passed an exam or not:
(grade >= 50) ? “You passed!” : “Failed”
The if operator can be used in conjunction with almost all other operators. For example, if you would like to get the result that passes multiple conditions (all conditions must apply):
(Condition A && Condition B) ? Return value if both are true : Return value if either is false
It can also be nested, which is equivalent to an ‘if...else if…else if…’ statement, if you would like to check against a sequence of conditions. For example, the expression below will return the maximum value:
(Value A > Value B) ? ((Value A > Value C) ? Value A : Value C) : ((Value B > Value C) ? Value B : Value C)
In other words:
does a = b ? yes : does a = c ? yes : does a = d ? yes : no
Breaking it down with Examples
The Status Formula
Remember, the "conditional operators" (conditional formulas in Cascade) work in this way
does a = b ? yes : no
The formula for status is:
( context.ahead_behind_value < 0) ? 0 : (context.ahead_behind_value >= 0 && context.progress >= context.target) ? 2 : 1
What this formula is saying is:
( context.ahead_behind_value < 0) ?
Translation: Is ahead/behind value less than zero?
0 :
Translation: If yes, give me "0" (0=Behind)
(context.ahead_behind_value >= 0 && context.progress >= context.target) ? 2
Translation: If no (ahead/behind value is NOT greater than zero)
Then is ahead/behind value greater than zero AND the progress is exactly on target?
If yes, then give me "2" (2=Complete).
: 1
Translation: If no, (meaning the ahead/behind value is greater than zero but progress is not exactly on final target), give me 1 (1=On track)
RAG Progress Formula (Red, Amber, Green):
Feel free to copy and paste this formula into a formula attribute, and set your number groupings to 0 if it should be green, 1 if Amber and 4 if Red:
(((context.progress-context.ahead_behind_value)<0) ? (-(context.ahead_behind_value/(context.progress-context.ahead_behind_value))) : (context.ahead_behind_value/(context.progress-context.ahead_behind_value))) <= -0.1 ? 4 : ((((context.progress-context.ahead_behind_value)<0) ? (-(context.ahead_behind_value/(context.progress-context.ahead_behind_value))) : (context.ahead_behind_value/(context.progress-context.ahead_behind_value))) < 0 ? 1 : 0)