This article outlines all the key things you need to know to use Wovex formulas.

These are deep calculation functions that are available on Wovex Unlocked.

Formulas are a very flexible way of defining how you want one or a number of measures to be summed/multiplied/averaged for example to create an actual automatically from complex sets of data. 


*These are only used when the standard Calculation Approaches are not enough to do what you want to do with equations on measures for your reporting. Always start with calculation approaches first to see if the out-of-the-box options are enough for your requirements.


If you need to use a formula to create your measure calculations you can turn on Formula Info in Manage Actuals to edit and create a Formula.


1. Introduction

You need some way of planning and in Benefits Realisation Management, Benefit Measures are key.

The Timeseries Data on a Tracking Measure can use formulas to calculate values from Tracking (and other) Measures or Data Items.


Benefit Measures define what to measure: so we can plan for and manage realisation of the benefits expected.


Let's look at some terminology for a Benefit Measure:


Baseline: A number that indicates where we've started from, so we can compare future progress against. E.g. "We started with a Baseline of 10 and now we are at 2".


Goal: A number that indicates where we expect to be at any point in time. The Goal, or Forecast for that point in time. There can be many different Goals set at different points in the future, as you may expect changes in the number to come in bit by bit. e.g. "At the end we want to have a value of 2 and we expect changes to 8, 6, 4 over the next 12 months".


Actual: A number that indicates where we actually are at any point in time. The Actual. This can be compared to the Goal to understand if we are on target, behind or ahead. The difference between the Goal and Actual is the Variance. "Today we actually have a value of 7, the Goal was 6 so we have a variance of +1".  


Status: Statuses can be used to focus attention on the most poorly performing and important Benefit Measures. Wovex provides four colors to be used: Red, Amber, Green and Blue.
Rules are set-up for each Benefit Measure on how you want to use these.




2. Statuses

Let's look at four examples:


  1. No status is needed. It may be too early to agree on the status rules, or a status will add no value.
  2. Anything below a certain value is Green. Anything above a value is Red. Just having Red or Green is good for values you are looking to reduce - as long as it's lower than a certain value then that is good.
  3. Anything between two values is Green. Anything above or below these is Amber, Then if the value goes even higher or lower then it's Red. These are when Upper and Lower Control Limits are used to monitor Measures. These are popular when monitoring process speeds, weights or volumes in manufacturing, for example.
  4. Anything not at a specific value is Red. This is zero tolerance in terms of monitoring. The Actual must be exactly the same as the Goal to be Green.



So, these are just four of the many possible examples.


These colors can mean:


- Blue: no status available as not enough data is available or status has been set as not relevant for this Measure.

- Red: highest priority to look at and manage.

- Amber: need to manage back to green - not as critical as red.

- Green: on-track and within the tolerances and thresholds expected.


For example, you can say, a Goal is 6. 

If it is higher than 8 then the Status should be Amber.


If it is higher than 10 then the Status should be Red.

Any value of 8 or less has a Status of Green.


For each point in time, you can set:

- 'Red above' e.g. 10.

- 'Amber above' e.g. 8.

- 'Amber below' e.g. set to -1 which means in Wovex, ignore this, so stay green.

- 'Red below' e.g. set to -1 which means in Wovex, ignore this, so stay green.


Statuses are one way to focus attention on poorly performing Benefit Measures. Others, such as the Achievement Score, Weighted Achievement Score, Percentage Left to Achieve report or Biggest Current Variances report, are available.

 


3. Dates & Measurement Intervals

We've spoken about the Benefit Measure having data between a range of dates and different points in time. 

Let's look at the range of dates first.


Start and End Dates: These are the range of dates you want to set Goals and collect Actuals for. 

For example, this can be a period of a month or 30 years. This very much depends on the project or program. 

As change is normally delivered at various milestones, or points in time, it makes sense that several Benefit Measures will also be tracked between different Start and End Dates. 



Is each change having the impact on the Benefit Measures when and to the degree expected?

So, different Benefit Measures within a project or program can start at different times and end at different times.


Another thing to consider is the Measurement Intervals. This is the spacing between each point in time.
Will you define a Goal and will collect an Actual value daily, weekly, monthly, yearly?

At each point in time, the value for Baseline, Goal, Actual, Variance and the Status rules like 'Red When Above' can be set. 

So there can be lots of dates and values for each Benefit Measure. This provides significant flexibility. Let's look at this in a bit more detail:



4. Using Data from Other Benefit Measures

Entering values for a Benefit Measure is great, but sometimes you want to use values from other places. This is where formulas come in.


Examples include:




- Duplicate: Copying the exact values from another Benefit Measure, to save time. Just the Actuals are entered separately.


Enter on Benefit Measure C as a formula in the Actual value:

           Actual  ([  Benefit Measure A  ])


This formula copies all the Actual values from Benefit Measure A to the matching Actual values on Benefit Measure C



Real example using an Objects Name:

If you know the Name of the Benefit Measure A, then use: actual([@(Name of Benefit Measure A)])



  1. - actual is the function to use. Others include baseline, 
  2. - ([   ]) define the area with the objects and fields in to be used by the function are found.
  3. - @(  ) shows that names or titles of objects is being used
  4. - Name of Benefit Measure A  is the name of the object.



Another real example using an Objects Extra ID value:

If you know the value in the Extra-ID field (if one has been entered by a user), then this can be used. 

This is a helpful alternative if the name of a Benefit Measure is likely to change. 

For an Extra-ID, then use: actual([#(Extra-ID value)])



  1. - actual is the function to use. Others include baseline, 
  2. - ([   ]) define the area with the objects and fields in to be used by the function are found.
  3. - #(  ) shows the Extra-ID of objects is being used
  4. - Extra-ID value   is the value in the Extra-ID field.



A final example, using an Objects internal Public-ID value:

If you know the value in the Public-ID field (an internal code for each object Wovex creates, like:

7977c1f3-9f3a-4769-ab70-a9a44dc07f6d) then this can be used. 


This is a foolproof alternative if the Name and Extra-ID of a Benefit Measure are likely to change. 

For a Public-ID, then use: actual([$(Public-ID value)])



  1. - actual is the function to use. Others include baseline, 
  2. - ([   ]) define the area with the objects and fields in to be used by the function are found.
  3. - $(  ) shows the Public-ID of objects is being used
  4. - Public-ID value   is the value in the Public-ID field.



If you know the name of the Benefit Measure A, then use: actual([Name of Benefit Measure A])





- Sum: Totaling the values from many Benefit Measures into a separate Measure for reporting.



Enter on Benefit Measure C as a formula in the Actual value:


           Actual  ([  Benefit Measure A  ])    +    Actual  ([  Benefit Measure B  ])


This formula adds each Actual value from Benefit Measure A to each Actual value from Benefit Measure B, then stores the result in  Benefit Measure C

Instead of + you can also use - / * %. More than two Benefit Measures can be added as well.  





- Convert: Converting one or more Benefit Measure values into a different unit of measure e.g. from US Dollars to Euros, using a conversion rate stored in another separate Measure. 


Enter on Benefit Measure C as a formula in the Actual value:


           Actual  ([  Benefit Measure A  ])    *    ([  Object X  .  Field 1  ])


This formula multiplies each Actual value from Benefit Measure A, with the fixed value stored in Field 1 on Object 1 and stores the result in the matching Actual values on Benefit Measure C

Again, you can use + - / % and more instead of *. Also, a mix of fixed values and Actual values from many different objects can be used as well.





- Ratio: Creating a Ratio from multiple Measures e.g. using one Measure for the Denominator, another for an Numerator.


Enter on Benefit Measure C as a formula in the Actual value:


       Actual  ([  Benefit Measure A  ])    /    Actual  ([  Benefit Measure B  ])


Here Benefit Measure A or B can be a set of value that aren't just for one project or program, but applicable across the organization. So you can set up a series of reference values that can be used to calculate ratios.

This formula divides each Actual value from Benefit Measure A by the Actual value from Benefit Measure Bthen stores the result in Benefit Measure C

As before, other mathematical operators and values can be used.





- Logic: Using logic Summing them then 


Enter on Benefit Measure C as a formula in the Actual value:

         if(3 % 2 = 1, 'value is true', 'value is false') Actual  ([  Benefit Measure A  ])    /    Actual  ([  Benefit Measure B  ])


Here Benefit Measure A or B can be a set of value that aren't just for one project or program, but applicable across the organization. So you can set up a series of reference values that can be used to calculate ratios.

This formula divides each Actual value from Benefit Measure A by the Actual value from Benefit Measure Bthen stores the result in Benefit Measure C

As before, other mathematical operators and values can be used.





How Formulas can be built


There is often a need to calculate values for a baseline, goal/forecast, actual etc. using values from elsewhere. This means that quite complex scenarios for measures, forecasting, tracking and reporting need to be met.

The Wovex system has standard calculation functions built-in, formulas give you the chance to create your own.


This will introduce you to formulas and will help you to set them up and use. 



1. Using Data - Input 

To calculate values using formulas you need data as input.

This input data may have been entered by a user, imported from another system, calculated by the software or the results of a different formula elsewhere (nesting formulas).


There are two main types of data:

- Fixed:            This can be values stored in a field that the calculations use. For example, hours in week or current rate of pay for a certain grade of person.

- Time-series:  Here there will be a number of time-periods for a value that can change over time. For example, the can be monthly Forecasts for a Benefit Measure for 12 months.

                         The field is 'Forecast' with monthly intervals and each will have a date. 



2. Data Output - Storing the Results

Formulas can be added to and the results stored in seven fields. These are all on a Benefit Measure.

These are Time-series fields that are for baselines, forecasts, actuals and RAG status thresholds.


Actual                     - the value captured as actuals/current values for a Benefit Measure. 

Baseline                 - the value of the Benefit Measure if the change hadn't happened, often a fixed value over all the Time-series. 

Forecast                 - the expected future value of the Benefit Measure as change happens - typically increasing or decreasing over-time.


RedBelow               - an Actual entered is checked against this value. If the Actual is less than this value then a Red RAG status will be shown (Red when below).

AmberBelow          - an Actual entered is checked against this value. If the Actual is less than this value then a Orange RAG status will be shown (Amber when below). 

AmberAbove          - an Actual entered is checked against this value. If the Actual is more than this value then a Orange RAG status will be shown (Amber when above).

RedAbove               - an Actual entered is checked against this value. If the Actual is more than this value then a Red RAG status will be shown (Red when above). 


Formulas are entered into these fields either directly or using a wizard.



3. Functions used by Benefit Measures 

The following are specific functions to help with Time-Series data. They can help you use Time-Series data from other objects and use them on a new one.

Seven common time-series fields on a Benefit Measure are: 


For Standard Data:

  • Actual:                    Actual(['name or ID of objects to take the data from' [operator] 'name 2'...]) 
  • Baseline:                Baseline(['name or ID of objects to take the data from' [operator] 'name 2'...])
  • Forecast:                Forecast(['name or ID of objects to take the data from' [operator] 'name 2'...])


For RAG Statuses:

  • RedBelow:          RedAbove(['name or ID of objects to take the data from' [operator] 'name 2'...])
  • AmberBelow:     AmberBelow(['name or ID of objects to take the data from' [operator] 'name 2'...])
  • AmberAbove:    AmberAbove(['name or ID of objects to take the data from' [operator] 'name 2'...])
  • RedAbove:         RedAbove(['name or ID of objects to take the data from' [operator] 'name 2'...])


General Functions:

  • LatestSum: sum a number of the latest entries of a timeseries e.g. Actual, Baseline, Forecast, RedBelow etc

                                            LatestSum(['name or ID of a Benefit Measure'],'Field to look at the time-series data for e.g. Actual',number of previous entries to use e.g. 3 ) 

  • LatestAverage: averages a number of the latest entries of a timeseries e.g. Actual, Baseline, Forecast, RedBelow etc.

                                            LatestAverage(['name or ID of a Benefit Measure'],'Field to look at the time-series data for e.g. Actual',number of previous entries to use e.g. 3 ) 





IMPORTANT: Please avoid using ( ) in the names or titles of any objects that will be used in formulas. Use [ ] or { } instead.

There is some logic in Wovex to protect and convert ( ) to other symbols but please used alternatives as much as possible to avoid any intended formula consequences.




4. Identifying Models, Objects and Fields


As mentioned in '2. Using Data - Input' there are two main types of data - Fixed and Time-series. 

Fixed fields can be on any type of object - Benefit Measure, Stakeholder, Note etc. 

Time-series fields are just on Benefit Measures.


There are different ways of identifying these, let's take some examples:


1. [@(Effort per Incident).Priority]

This inserts the value found on the object titled 'Effort per Incident' and in the field 'Priority'. 


2. [@(Effort per Incident).Priority]*10

This muliplies by 10 the the value found on the object titled 'Effort per Incident' and in the field 'Priority'. 


Hint: The formulas are case sensitive - instead of Priorities, you entered priorities with a lower-case p then you would have got an error message.


3. [@(Effort per Incident).Priority]*[@(Number of Incidents).Priority]

This multiplies the value found in the Priority field together from two different objects.

Different operators can be used like /+-

The values from many different objects can be referred to in this way, here just two are show.




Your Super-User will have identified the fields to use for formulas, or agree your needs with them.

Wovex or your Wovex partner can also help your Super User identify the best fields to use depending on your needs.


Each model, object and field in the system can be identified in different ways. These are easy to learn.



4.1. Models



An Example

Using the name/title of an object to identify it:

Baseline([@(Number of days off due to family/friend events)]) + Baseline([@(% increase in number of people I interact with)]) + Baseline([@(Average travel time per month)])


Where Baseline( ) is the function. To go and find an object and use values from its Baseline field.

There is an object referred to by name - Number of days off due to family/friend events, to find a match.

The search uses a name because of the @(   ) characters. refers to the name or title field.  (  ) has the name/title text to match in.


Baseline([@(Number of days off due to family/friend events)])



Using the Extra ID of an object to identify it:

Each object can have an extra ID to identify it added.  

This can be referenced in a formula to identify the object instead. For example:


Baseline([#(Benefit-57a)])





Using field names rather than the functions:

You can also use [#(Benefit-57a).Priority]


Where Priority is the name of the field on the object with the Extra Id = Benefit-57a.

Note: Names are case sensitive, so 'priority' as the field name will give an error, 'Priority' will be okay.


This is how you can use in the formulas any field with a numerical value.



Additional Functions - LatestSum and LatestAverage:


These take a list of values from the functions already, e.g. Actual, Baseline, for a Benefit Measure. It then either sums or averages the current and a number of previous entries.

These functions have 3 parameters:
1. Benefit Measure
2. The function/were the list of values are (for example, Actual)
3. Count of move many entries to use


Example: LatestSum([@(measure 3)], 'Actual', 4) + 1


This looks at the Benefit Measure called 'measure 3' and sums the last 4 entries. Then it adds 1 to the total.



Referencing other models data:

For some of your calculations you may want to look at and use the data from outside the same model. 
For example, to sum the cost savings from measures across three models, called 'People Change', 'HR Transformation' and 'Infrastructure Transformation' and store the result in a fourth model.


Note: This is only supported currently for models on the cloud.

You do this in the cloud by adding an extra reference to the model e.g. ([@(People Change).@(Benefit 5).Priority]) 

...looks at the value in the field internally called 'Priority' on the object 'Benefit 5' within the model called 'People Change'.


Another example: Actual([@(People Change).@(Measure 2)]) 

...takes the Actual values from the object 'Measure 2' on the model 'People Change'.



Extra Reference: 

Objects created on a Map have a Map ID automatically added, for example M-2, O-5, Obj-1. 
This Map ID field exists on each object* and its value can be added and changed manually in the cloud.


This is an extra option for building a formulas, useful when you have objects with the same name in a Model.

is used instead of @ in the example:



Example 1:


Object Name:     % increase in number of people I interact with

Object Map ID:   M-2


   Baseline([@(% increase in number of people I interact with)])


Is the same as:

 


   Baseline([$(M-2)])




Example 2:


Model Name:      People Change

Object Name:     Average travel time per month

Object Map ID:   M-2


    Actual([@(People Change).@(Average travel time per month)])  


Is the same as:

 

   Actual([@(People Change).$(M-2)])  



Example 3:


Another example:

Model Name:      People Change

Object Name:     Increased Revenue

Object Map ID:   B-15



   [@(People Change).@(Increased Revenue).Priority]


Is the same as:


   [@(People Change).$(B-15).Priority]



In: 

If 'Measure 2' had a Map ID of BM-2.

Use Actual([@(People Change).$(Out-5)]) 




Use Actual([@(People Change).@(Measure 2)]) 




using the models Wovex ID, the formula could look like:

Actual([$(35c50709-1f3b-4963-92b1-47ce82703ef4).@(Measure 2)]) 


Notice the use of the $ instead of the @.


By the way, a model also has a Map ID A Wovex ID looks like: 35c50709-1f3b-4963-92b1-47ce82703ef4

They are shown when you do a model search (Models & Profiles --> My Models).



The following will use all the Actual Value from 'measure 3' object on the same model and add the value found in the field internally called CustomColumnValue1 on the object called Benefit 1 within the model named 'People Change':

On the model 'Infrastructure Transformation' there is a Benefit Measure with the formula in the Actual field:

[@(People Change).@(Benefit1).CustomColumnValue1] + Actual([@(measure 3)])


Where @(People Change) refers to the People Change model.

@(Benefit1) refers to the object called Benefit1 in that model.

.CustomColumnValue1 refers to the internal field name of that object.


Remember: You can use @(Program Title) and $(Wovex ID) syntax to identify the other model, for example:



Top Tips

1. Every Benefit Measure in the cloud that has calculations on needs to have a 'Starts On' and 'Ends On' date and 'Generate Data' needs to have been selected - this creates the time slots to place the found data within.

See: https://www.screencast.com/t/x7GrOojRb


2. Once the formula is entered, then it can be calculated.

To do this, go to 'My Account' and then 'My Models'. 

Chose the model to run the calculation for - select the icon with the calculator on.


This calculates the data for the model and populates the fields..




5.Operators

Expressions can be combined using operators. Each operator as a precedence priority. Here is the list of those expression's priority.

  1. primary
  2. unary
  3. power
  4. multiplicative
  5. additive
  6. relational
  7. logical

Logical

These operators can do some logical comparison between other expressions:

  • or, ||
  • and, &&
  true or false and true

The and operator has more priority than the or, thus in the example above, false and true is evaluated first.

Relational

  • =, ==, !=, <>
  • <, <=, >, >=
  1 < 2

Additive

  • +, -
  1 + 2 - 3

Multiplicative

  • *, /, %
 1 * 2 % 3

Bitwise

  • & (bitwise and), | (bitwise or), ^(bitwise xor), << (left shift), >>(right shift)
  2 >> 3

Unary

  • !, not, -, ~ (bitwise not)
  not true

Primary

  2 * ( 3 + 2 )



Values

A value is a terminal token representing a concrete element. This can be:

  • an integer
  • a floating point number
  • a date time
  • a boolean
  • a string
  • a function
  • a parameter

Integers

They are represented using numbers. 


123456

They are evaluated as Int32.

Floating point numbers

Use the dot to define the decimal part. 


123.456
.123

They are evaluated as Decimal.

Scientific notation

You can use the e to define power of ten (10^).

1.22e1
1e2
1e+2
1e-2
.1e-2
1e10

They are evaluated as Double

Dates and Times

Must be enclosed between sharps. 


#2008/01/31# // for en-US culture

The are evaluated as DateTime. Wovex uses the current Culture to evaluate them.

Booleans

Booleans can be either true or false.


true

Strings

Any character between single quotes "'" are evaluated as String


'hello'

You can escape special characters using \\, \', \n, \r, \t.


Function

A function is made of a name followed by braces, containing optionally any value as arguments.


  Abs(1), doSomehting(1, 'dummy')

 e.g. 

#Actual

#Baseline
#Forecast
#RedBelow
#AmberBelow
#AmberAbove
#RedAbove


Parameters

A parameter as a name, and can be optionally contained inside brackets.


  2 + x, 2 + [x]



The framework includes a set of already implemented functions.


Name
Description



Usage
Result
Abs
Returns the absolute value of a specified number.
Abs(-1)
1M
Acos
Returns the angle whose cosine is the specified number.
Acos(1)
0d
Asin
Returns the angle whose sine is the specified number.
Asin(0)
0d
Atan
Returns the angle whose tangent is the specified number.
Atan(0)
0d
Ceiling
Returns the smallest integer greater than or equal to the specified number.
Ceiling(1.5)
2d
Cos
Returns the cosine of the specified angle.
Cos(0)
1d
Exp
Returns e raised to the specified power.
Exp(0)
1d
Floor
Returns the largest integer less than or equal to the specified number.
Floor(1.5)
1d
IEEERemainder
Returns the remainder resulting from the division of a specified number by another specified number.
IEEERemainder(3, 2)
-1d
Log
Returns the logarithm of a specified number.
Log(1, 10)
0d
Log10
Returns the base 10 logarithm of a specified number.
Log10(1)
0d
Max
Returns the larger of two specified numbers.
Max(1, 2)
2
Min
Returns the smaller of two numbers.
Min(1, 2)
1
Pow
Returns a specified number raised to the specified power.
Pow(3, 2)
9d
Round
Rounds a value to the nearest integer or specified number of decimal places. The mid number behaviour can be changed by using EvaluateOption.RoundAwayFromZero during construction of the Expression object.
Round(3.222, 2)
3.22d
Sign
Returns a value indicating the sign of a number.
Sign(-10)
-1
Sin
Returns the sine of the specified angle.
Sin(0)
0d
Sqrt
Returns the square root of a specified number.
Sqrt(4)
2d
Tan
Returns the tangent of the specified angle.
Tan(0)
0d
Truncate
Calculates the integral part of a number.
Truncate(1.7)
1

It also includes other general purpose ones.


Name
Description
Usage
Result
in
Returns whether an element is in a set of values.
in(1 + 1, 1, 2, 3)
true
if
Returns a value based on a condition.
if(3 % 2 = 1, 'value is true', 'value is false')
'value is true'




Static parameters

Static parameters are values which can be defined before the evaluation of an expression.

These parameters can be accesed using the Parameters dictionary of the Expression instance.


  Expression e = new Expression("2 * [x] ^ 2 + 5 * [y]");
  e.Parameters["x"] = 5;
  e.Parameters["y"] = 1;

  Console.WriteLine(e.Evaluate());

Parameters can be useful when a value is unknown at compile time, or when performance is important and the parsing can be saved for further calculations.

Expression parameters

Expressions can be splitted into several ones by defining expression parameters. Those parameters are not simple values but Expression instances themselves.


  Expression volume = new Expression("[surface] * h");
  Expression surface = new Expression("[l] * [L]");
  volume.Parameters["surface"] = surface;
  surface.Parameters["l"] = 1;
  surface.Parameters["L"] = 2;

Dynamic parameters

Sometimes parameters can be even more complex to evaluate and need a dedicated method to be evaluated. This can be done by intercepting there evaluation using the EvaluateParameter event published on Expression instances. Thus, each time a parameter is not defined in the dictionary, this event is called to try to resolve the value.


  Expression e = new Expression("Round(Pow([Pi], 2) + Pow([Pi], 2) + [X], 2)");

  e.Parameters["Pi2"] = new Expression("Pi * [Pi]");
  e.Parameters["X"] = 10;

  e.EvaluateParameter += delegate(string name, ParameterArgs args)
    {
      if (name == "Pi")
        args.Result = 3.14;
    };

Square brackets parameters

Parameters in between square brackets can contain special characters like spaces, dots, and also start with digits.


  Expression e = new Expression("[My First Parameter] + [My Second Parameter]");

Multi-valued parameters

When parameters are IEnumerable and the EvaluationOptions.IterateParameters is used, the result is an IList made of the evaluation of each value in the parameter.


 Expression e = new Expression("(a * b) ^ c", EvaluateOption.IterateParameters);
 e.Parameters["a"] = new int[] { 1, 2, 3, 4, 5 };
 e.Parameters["b"] = new int[] { 6, 7, 8, 9, 0 };
 e.Parameters["c"] = 3;

 foreach (var result in (IList)e.Evaluate())
 {
     Console.WriteLine(result);
 }

 //  216
 //  2744
 //  13824
 //  46656
 //  0


*Super-Users need to know that the fields Map ID (on objects and added automatically when added to a Map) and Model ID (when a new Model is created) are called PubicID on the Entities.