Formulae let you combine answers to create new answers dynamically.
Formula Example
For example, consider the following Formula metric:
Formula
Answers to this metric are calculated using the answers to two other metrics using the following formula:
m1 / m2 * 100
Here is an answer for a specific company (Volkswagen AG) and year (2019) generated by that formula:
You can expand that answer bar to see the the formula and inputs used to reach the answer value.
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 you may wish to specify more. You can select a metric to use in your formula using the “Add Variable” button in the formula editor.
If only the metric 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 m1 (Climate_Action_Research+Total_renewable_energy_consumption) and VW’s 2019 answer for m2 (Global_Reporting_Initiative+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.
Variable Options
In the formula editor, each variable has an options column. You can edit options for variables one at a time using the pencil icon in the options cell, or you can change the options in bulk using the dropdown below the table.
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. 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
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.
The company can be set to: - a specific company. This can be useful if, for example, you want to use a given company as a benchmark. - a company group - a relationship metric
“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 choose “calculate no result for this company/year” in the Unknown section of
the options editor.
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 choose what word you want to appear as the variable value within the formula.
If every input variable is configured to provide a value for both 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.
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 your browser can run your formula with sample inputs instantly in your browser. However, for security reasons, your formula will NOT be run directly in anyone else’s browser. 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:
- a number, eg 42
- a string, eg “Yes”
- 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 - 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,
year: 2010..2015
… will return an array of numbers for a Number metric.
But for a multicategory metric, it will return an array of array 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 = 5is different froma == 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 wantif a == b, NOTif 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).
Unknown answers (Meaning the answer has been researched and specifically denoted as Unknown).
- 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
Geography
- iloRegion(regionString) # returns string
- country(regionString) # returns string