Understanding Formulas

Formula Overview

A formula is a special data type that allows our engine to perform calculations based on other property values or variables. They are used to define a relationship between different properties and compute a result based on that relationship.

Functions in Formulas

Functions can be invoked within formulas to specify which limit should be respected. The available functions are Min and Max, aliased as fnMinUpperLimit and fnMaxUpperLimit. In the context of tiered rates, the upper limit refers to the consumption or demand limit specified for each tier within a tariff rate. By finding the maximum upper limit, you can identify the highest limit value among the tiers. Conversely, you can identify the lowest limit value among the tiers by finding the minimum upper limit.

Anatomy of a Formula

This is an example of a relatively simple FORMULA that takes the number of billing days and divides that value by 365 (days in a standard calendar year).

GET /rest/public/properties/billingPeriodYearFactor

{
    "status": "success",
    "count": 1,
    "type": "PropertyKey",
    "results": [
        {
            "keyName": "billingPeriodYearFactor",
            "displayName": "Billing Period Factor",
            "family": "formula",
            "keyspace": "tariff",
            "description": "Total Billing Days divided by 365",
            "dataType": "FORMULA",
            "formulaDetail": "( #billingPeriod.days  / 365.00 )"
        }
    ]
}

A DEMAND type PropertyKey can contain a formula or be a part of another formula. This example adds the results from two different PropertyKey objects to return a single result.

GET /rest/public/properties/billing2Demand1517?fields=ext

{
    "status": "success",
    "count": 1,
    "type": "PropertyKey",
    "results": [
        {
            "keyName": "billing2Demand1517",
            "displayName": "Total Billing Demand",
            "family": "formula",
            "keyspace": "tariff",
            "description": "Total kW metered during the billing period",
            "dataType": "DEMAND",
            "formulaDetail": "#offPeakBillingDemand1517 + #onPeakBillingDemand1517",
            "lookbackPeriod": "",
            "entityId": 1517,
            "entityType": "LSE",
            "privacy": "PUBLIC"
        }
    ]
}

In each example, we can see how the "formulaDetail" field provides the actual mathematical expression used in the formula.

Formula Walkthrough

Let's take everything we have covered thus far and walk through a more complex formula example.

GET /rest/public/properties/ratchet2wBillingDemand2437?fields=ext

{
    "status": "success",
    "count": 1,
    "type": "PropertyKey",
    "results": [
        {
            "keyName": "ratchet2wBillingDemand2437",
            "displayName": "Ratchet Billing Demand",
            "family": "ratchetDemand",
            "keyspace": "tariff",
            "description": "The highest of the 1) On-peak 15 minute interval in the previous month, 2) 90% of the on-peak kVA demand, 3) Off-peak 15 minute interval discounted by 55% and 4) 90% of the off-peak kVA demand discounted by 55%.",
            "dataType": "DEMAND",
            "quantityUnit": "",
            "formulaDetail": "#fnMaxUpperLimit ( { #winterOnPeakBillingDemand2437 , #winterOnPeakKVADemand2437  * 0.9 , #winterOffPeakBillingDemand2437 * 0.45 , #winterOffPeakKVADemand2437 * 0.405 } )",
            "lookbackPeriod": "",
            "entityId": 2437,
            "entityType": "LSE",
            "privacy": "PUBLIC"
        }
    ]
}

Observing the formulaDetail key we can see the use of the fnMaxUpperLimit function to find a maximum value between four PropertyKey objects.

Great, we now know what logic will deduce this specific Ratchet Billing Demand. If we go one step further we can identify each of the PropertyKey objects above and understand how those values are calculated and fed into this overarching formula.

We will focus on winterOffPeakBillingDemand2437 specifically, but note that this process would be the same for the others. Using the Property Key API we pass in our keyName and instruct the API to return all available fields via fields=ext.

GET /rest/public/properties/winterOffPeakBillingDemand2437?fields=ext

{
    "status": "success",
    "count": 1,
    "type": "PropertyKey",
    "results": [
        {
            "keyName": "winterOffPeakBillingDemand2437",
            "displayName": "Winter Off-Peak Billing Demand",
            "family": "peakDemand",
            "keyspace": "tariff",
            "description": "The peak 15 minute interval in the Off-Peak period during the previous month.",
            "dataType": "DEMAND",
            "quantityUnit": "kW",
            "lookbackIntervalQuantity": 15,
            "lookbackQuantity": 1,
            "lookbackPeriod": "BILLING_PERIOD",
            "lookbackTimeOfUseId": 1102,
            "entityId": 2437,
            "entityType": "LSE",
            "privacy": "PUBLIC"
        }
    ]
}

The description field will provide us with a human-readable overview of what this PropertyKey represents, in this case, "The peak 15 minute interval in the Off-Peak period during the previous month.". Further down we can see several fields prefixed by "lookback" which provide additional context into how usage data is observed to derive the peak interval.

Following these steps we can observe the other PropertyKey objects and better understand how our usage data is being used to determine rates within our selected tariff.