Write Metric Formulae

Create answers dynamically from other answers.

Formulae let you combine answers to create new answers dynamically.

Formula Example

For example, consider the following Formula metric:

23 59

Answers to this metric are calculated using the answers to two other metrics using the following formula:

= m1 = m2 = m1 / m2 * 100

Here is an answer for a specific company generated by that formula:

If you were to edit that formula, you would see it looks like this:

# CoffeeScript
m1 = {{Climate Action Research+Total renewable energy consumption}}
m2 = {{Global Reporting Initiative+Total energy consumption, GRI 302-1-e (formerly G4-EN3-e)}}
m1 / m2 * 100

Parts of a Formula

The formula has three parts. To understand them, you must first understand that WikiRate is transitioning towards a major Formula upgrade. So some things that look a little funny now will be cleaner soon.

  • type The formula begins with # CoffeeScript, which is just a flag that indicates that the Formula is written in the new “CoffeeScript” style to which we’re transitioning. For now all new formulae should use this flag. After the transition is complete, we will remove the flags automatically.
  • variables The second section assigns variable names (m1, m2, etc) to metrics. This system will soon be upgraded too. It will support the upgrade if you use the simple variable naming pattern.
  • algorithm The third section is the formula itself. Whatever value is returned by the final line of the formula will be the resultant answer.

For every answer of the +employees metric, this formula would create a new answer for the +increment metric that was one greater. So if My Company’s 2015 answer for +employees was 100, then its 2015 answer for +increment would be 101.

Variables

The variables section defines variables to which answer values will be assigned. In all cases, you will need to specify the answers’ metric. In some cases (discussed inline below), you may wish to specify more.

Specifying metrics

A metric can be represented within a formula as a metric’s full name within double curly brackets, eg:

        {{CDP+Scope 1 Emissions}}

Because these names can be rather long, the formula interface provides a shortcut: when using the “add metric” button to find metrics, a temporary short name is provided, eg M0, M1…. You can then use those short names in the formula, eg:

        {{M0}}

Upon saving, the name will be converted to the full name. Note that if you already know the full name, you can type it directly in the formula interface and skip the “add metric” step.

If only the metric name is specified, then Formula metric answers for a given year will be based on variable metric answers of the same year.

For example, the simple answer above shows the 2019 answer calculated for Volkwagen (VW). To arrive at that answer, we used VW’s 2019 answer for metric 1 (Total renewable energy consumption) and VW’s 2019 answer for metric 2 (Total energy consumption, GRI 302-1-e (formerly G4-EN3-e)).

Notice that the formula mentions neither the company nor the year; instead, it specifies the metrics, and WikiRate automatically generates answers for all companies/years for which all the needed answers are available.

Specifying Years

But what if you want to use answers from different years in your formula? For example, what if you wanted to know how a company’s renewable energy consumption changed from one year to the next?

In that case you can specify answer’s year:

m1 = {{METRIC_NAME}} # year of output answer
m2 = {{METRIC_NAME| year: -1}} # previous year
m1 - m2

There are several ways to specify years:

year: -2          # 2 years before the output answer
year: 3           # 3 years after the output answer
year: 2015        # absolute year (always use the 2015 value as input)
year: latest      # always use the latest answer (regardless of the output year)

If you specify a list of years, the variable will be set to an array of answer values.

year: -2..0        # 2 years before, 1 year before, and the year of the output answer
year: 2011..2013   # always use the answers for 2011, 2012, and 2013
year: all          # puts all available answers in an array

Specifying Companies

Companies are handled in much the same way as years. By default an output answer for a given company is based on input answers for the same company, but this can be changed. This can be useful if, for example, you want to use a given company as a benchmark.

For example, to calculate the “total revenue relative to Apple Inc”:

m1 = {{Core+Revenue}}
m2 = {{Core+Revenue | company: Apple Inc}}
m1 / m2

It’s also possible to use relationship metrics to specify a list companies, which will return an array of values all the related companies.

For example, to get an array of the revenue amounts of all suppliers of a company, you could do something like this:

m1 = {{Core+Revenue | company: Related[Commons+Supplied by] }} ]

Note: this syntax will soon be simplified. If you use this syntax in your formula, we will be updating your formula when the new syntax is supported.

“Unknown” and “Not researched” values

WikiRate makes a distinction between answers that are Not researched, meaning that there is no record of anyone attempting to find an answer for a given metric/company/year, as opposed to those that are Unknown, which someone has attempted to find without success.

As mentioned WikiRate will by default generate results for all companies/years for which all input answers are researched. This includes those for which the input answer is “Unknown.” By default, if the answer to any of a formula’s input values is unknown, then the output will be Unknown.

However, you can override this default behavior by configuring the unknown and not_researched options . For example, perhaps you do not want a result to be generated if an input is Unknown. In that case you could write the following:

{{METRIC_NAME| unknown: no_result }}

On the other hand, you may in some cases wish to generate an answer even if a given input is not researched. This is often the case in metrics measuring disclosure, for example. For such cases you could use something like the following:

{{METRIC_NAME| unknown: Unknown; not_researched: Unknown }}

With the above configuration, both unknown and not researched inputs would set the variable to the string "Unknown" and allow the formula to process it. (Note that this is different from the default behavior of the unknown setting, which produces the result Unknown without running the formula at all. That behavior can be configured explicitly using unknown: result_unknown.)

If every input variable is configured to support unknown and not researched answers, WikiRate will still only produce outputs for those companies/years that have at least one answer available. Otherwise, WikiRate would be flooded with nonanswers. (At the time of writing, generating a non-answer for every possible year/company combination for just one metric would double the number of answers on the site). However, if the output metric in question (not the formula but the metric itself) has applicability restrictions both to a fixed company group and a fixed set of years, then outputs can be generated for those companies/years even if no input is available.

Algorithm

Once you have your variables set, you are ready to write your formula.

As is implied by the # CoffeeScript flag at the beginning of your formula, WikiRate formulae are written in CoffeeScript. CoffeeScript is really just JavaScript with less punctuation; your formula will in fact be translated into JavaScript before it is run.

Every modern web browser can process JavaScript, which means in the near future we will be able to make it easy for you to test out your formula instantly in your browser. However, for security reasons, your formula will NOT be run directly in anyone else’s browser (unless they choose to edit the formula, and even then they must choose to execute it.) Instead, the answers will be calculated in a safe sandbox on WikiRate’s servers, and the results will then be shared on the site.

If the idea of writing JavaScript sounds intimidating, please understand that most formulae are very simple.

Answer value data types

The answers assigned to variables will be one of three types:

  1. a number, eg 42
  2. a string, eg “Yes”
  3. an array, eg [1, 2, 3]

The data type depends on the value type of the Metric. Number and Currency metrics generate numbers, Free text and Category metrics generate strings, and Multi-Category metrics generate arrays of strings.

If a year range is specified, then the answer value will return a array of the values of that type. For example,

\{{NUMBER_METRIC_NAME; year: 2010..2015}

… will return an array of numbers, while…

\{{MULTICATEGORY_METRIC_NAME; year: 2010..2015}

… will return an array of arrays of strings, eg [ ["A", "B"], ["B", "C"], ..]

Simple arithmetic

Most simple mathematical operations use these four operators:

+  # plus
-  # minus
*  # multiplied by
/  # divided by

For example:

5 + (m1 * m2) / m3 - 0.45

Simple logic

It’s also common to write metrics with simple “and/or” logic.

||  # or
&&  # and

For example, to take the value of the first variable with a value, you could write:

m1 || m2 || m3

To go a bit further, we can introduce if/then statements:

if m1 > 10
  "Yes"
else
  "No"

Note that the “whitespace” (spaces and line breaks and such) is meaningful in CoffeeScript.

We’re not going to provide a long intro to coding here, but there are some simple concepts that are worth mentioning:

  • it is usually best not to write a complex formula all in one go. Instead, break it into pieces. Start with a simple formula, making sure you’re getting the results you want, and then add complexity little by little.
  • a = 5 is different from a == 5. The first is assigning a value to a variable. The second is an equality test and is either true or false. In general, you will want if a == b, NOT if a = b.
  • Often confusion comes from forgetting what data type you’re working with. ["A"] == "A" is false, for example.

Functions

While most algorithms are quite simple, it’s possible to construct quite complex ones. When doing so, it’s often wise to make use of functions to keep your logic disentangled.

See [[Commons+Company Category] for an example of a complex function in action.

WikiRate provides a few built-in functions for common cases (this list is expected to grow).

  • isKnown(answer) # returns true unless answer is “Unknown”
  • numKnown(array) # returns the number of known items in an array
  • anyKnown(array) # returns true if there are any known items in the array