Formula indicators are an excellent way to handle percentages or to view various types of aggregates. If you are interested in a metric such as Percent of staff trained, the best way to manage this is to have two indicators: one for Number of staff trained and one for Number of staff. DevResults can compute the percentage automatically. Computing percentages and certain totals can reduce data entry, reduce human error, provide a more thorough audit trail, and give more appropriate comparisons of percentages between places, time periods, and activities.
This page will walk you through what you need to know to work with formulas:
- Configuring Formula Indicators
- What operations are supported in formulas?
- Can my formula reference specific disaggregation categories only?
- Do formulas support conditional logic?
- What if the features the indicators have in common are too detailed?
In the example below, we will compute
% of new users who pass certification exam
To do so, we will need to divide
# of new users certified (e.g. how many passed the certification exam)
# of new users administered the certification exam
To define your indicator with a formula, click From a Formula as your indicator's Data Source.
Once you make this selection, a Formula Editor box will appear for you to write the formula for the indicator.
You'll use square brackets like [this] to reference an indicator by its code. You can begin by typing a left square bracket: [ and you will get a dropdown of indicators in your site. You can scroll through these or start typing to narrow the list.
You can click or arrow to the indicator you want. Once you've selected the indicator you want, close it with another square bracket. The editor will display confirmation of the indicators you've referenced below, as well as any warnings or problems with the formula.
You can reference as many indicators as you need in the formula. Once you've entered your formula, before you navigate to another field, DevResults will check to see what the referenced indicators have in common. For formulas to compute, the indicators must be referencing the same disaggregations, reporting level, and so on. Our Common Features Evaluator will check all your referenced indicators to determine the most granular set of features they have in common.
It will present these common features in a pop-up for you to review. Click OK to accept these features.
The formula editor supports all standard mathematical operations (+, -, /, *) and recognizes parentheses. You can also use whole numbers and deicmals. For example, if you know that your # of new users certified indicator is consistently overcounted by 10%, you can multiple it by 0.9 to counteract this bias:
Absolutely! To reference specific disaggregation categories only, after you've selected or typed your indicator code, add a vertical bar or pipe: | (on standard English keyboards, this is grouped on the same key as \). The formula editor will then give you a relevant dropdown depending on the disaggregations assigned to that indicator:
Once you select the disaggregation category you want, the Indicators referenced section will update accordingly:
Yes. Conditional formulas use the IIF function. The Formula Editor will recognize inline if statements structured in this format:
IIF(<expression>, <truepart>, <falsepart>)
To create the expression, you can use:
|TRUE||Yes or 1|
|FALSE||No or 0|
In our example, maybe we only care about data where the # of new users certified is greater than 5, and if it's less than 5, to use 0 rather than the actual value. In that case, we would use something like this:
While you cannot make a formula indicator more detailed than the common features, you can always make it less granular. So, in our example, both the indicators in the formula have a Gender disaggregation and are set at the Location Reporting Level. The evaluator identified that, assigned the Gender disaggregation, and set my reporting level to Location. But if I don't want the gender disaggregation and I only want to see this calculated at the country level, I can change that:
Didn't answer your question? Please email us at firstname.lastname@example.org.