• Home
/
• Explore m3ter Documentation
/
• Pricing Metrics: Aggregations

Calculations Syntax and Supported Functions/Operators

This topic explains the syntax supported by the m3ter platform's calculation engine when you create calculations for Meter Derived Fields or Compound Aggregations. Details of supported functions and operators are also given.

Derived Fields - Referencing Fields

Derived Field calculations will typically reference Meter Data Fields. But you are not restricted to referencing Meter Data Fields in your calculations. You can also reference Custom Fields and Timestamp Fields, which greatly extends the scope of measures you can derive and subsequently use as the basis for the Aggregations you need to price your Product Plans.

Referencing Data Fields

The values of Data Fields in the measurement are available as variables, and can be referenced by the Data Field code, for example:

• myDataField

Referencing Custom Fields

You can reference Custom Fields you have defined for your Organization in a Derived Field calculation. You can define Custom Fields for your Organization and for the following entity types:

• Meter

• Product

• Account

• Account Plan

• Plan

• Plan Template

But note you can define a Custom Field for any of these entities at both the Organizational level and at the level of the individual entity. When you reference the Custom Field in a calculation, the value defined for the Custom Field at the Organizational level will be used unless you've also defined a Custom Field at the individual entity level. If a Custom Field has also been defined at the individual entity level, this value will be used. This allows you to set up default Custom Field values to use for each entity type in your Organization.

Example - Custom Fields for Accounts

For example, suppose you have five Accounts in your Organization: Acct1, Acct2, Acct3, Acct4, and Acct5. You expect to use a Derived Field Calculation that will reference a Custom Field for Accounts to add a weighting to each Account for usage data:

• You create a Custom Field for Accounts at the Organization level called cfAccount and set its value = 25.

• You create a Custom Field for Acct2 called cfAccount and set its value = 20.

• You create a Custom Field for Acct4 called cfAccount and set its value = 30.

• You do not create any Custom Fields for Acct1, Acct3, or Acct5.

You now reference the Custom Field in your calculation:

• account.cfAccount

The calculation will now be evaluated across the five Accounts using these values:

• Acct1 = 25

• Acct2 = 20

• Acct3 = 25

• Acct4 = 30

• Acct5 = 25

Format for Referencing

Here's the general format to use when referencing Custom Fields:

• organization.<fieldName>

• meter.<fieldName>

• product.<fieldName>

• account.<fieldName>

• accountPlan.<fieldName>

• plan.<fieldName>

• planTemplate.<fieldName>

Referencing Timestamp Fields

You can reference system variable Timestamp Fields in your Derived Field calculations. All timestamps are numeric values representing the appropriate date/times in Epoch milliseconds:

• ts

• ts.startOfMonth

• ts.endOfMonth

• ts.startOfMonthUTC

• ts.endOfMonthUTC

If the “ets” field - End Timestamp - is specified in the measurement, the following are also available:

• ets

• ets.startOfMonth

• ets.endOfMonth

• ets.startOfMonthUTC

• ets.endOfMonthUTC

Compound Aggregations - Referencing Values and Fields

Referencing Simple Aggregation Values

You can reference the resultant values of simple Aggregations in a Compound Aggregation calculation using a simple Aggregation's Code. The general format is:

aggregation.<aggregationCode>

The simple Aggregations you can reference when you create a Product-specific Compound Aggregation or a Global Compound Aggregation are the same:

• The calculation can reference all simple Aggregations – any Product-specific and any Global simple Aggregations.

Referencing Custom Fields

You can reference the values for Custom Fields defined at both Organization level or individual entity in your Compound Aggregation calculations for the following entities:

• organization.<fieldName>

• product.<fieldName>

• account.<fieldName>

• accountPlan.<fieldName>

• plan.<fieldName>

• planTemplate.<fieldName>

When you reference a Custom Field in a calculation, the value defined for the Custom Field at the Organizational level will be used unless you've also defined a Custom Field at the individual entity level. If a Custom Field has also been defined at the individual entity level, this value will be used.

Exception - Referencing Individual Meter Custom Fields

The exception to the general rule is:

• meter.<fieldName>

Even if you've created a Custom Field at the individual Meter level, the default value defined for the Custom Field created for Meter at the Organizational-level will be used for the calculation. This is because a Compound Aggregation might reference multiple Aggregations and therefore multiple Meters, which leaves it ambiguous as to which individual Meter's Custom Field value should be used for the calculation.

Referencing Bill Period Variables

You can reference the following twelve bill period variables in your Compound Aggregation calculations:

• ts.hoursInPlanArrearsPeriod

• ts.datesInPlanArrearsPeriod

• ts.daysInPlanArrearsPeriod

• ts.hoursInPlanAdvancePeriod

• ts.datesInPlanAdvancePeriod

• ts.daysInPlanAdvancePeriod

• ts.hoursInBillArrearsPeriod

• ts.datesInBillArrearsPeriod

• ts.daysInBillArrearsPeriod

• ts.hoursInBillAdvancePeriod

• ts.datesInBillAdvancePeriod

• ts.daysInBillAdvancePeriod

How these variables work to determine bill periods can be understood against three distinctions:

• In Arrears Period vs. In Advance Period:

  • In Arrears. The arrears period is the time when usage is accrued - the period between the previous bill and this one.

  • In Advance. The advance period is the period during which advance charges would appear on the bill - the period between this bill and the next one.

• Bill vs. Plan:

  • Bill Period. Bill period is determined by the billing frequency. For example, if you bill on the 15th of each month, then your bill period will be 28 days from 15th Feb - 15th March, or 30 days from 15th November - 15th December, and so on.

  • Plan Period. A Plan period may be shorter than the bill period, because the Plan might start or end mid-way through the bill period.

• Hours vs. Days vs. Dates. More care is needed with this 3-way distinction, because users might be working from different timezones, and timezones can change due to deliberate seasonal clock adjustments, such Daylight Saving Time changes. For example, on 27th March 2022 in the UK (Europe/London timezone) clocks were put forward one hour, so that day was only 23 hours long:

  • Hours. The number of whole hours in the period. Note this isn't "clock" hours - it's just any arbitrary 60-minute period. For example: 12:00 - 14:00 would be 2 hours; 12:37 - 14:37 would be 2 hours; but 12:37 - 14:36 would only be 1 hour - it's almost 2 hours, but not quite, and therefore only contains one entire 60-minute period.

    • Note that with the example above (using Europe/London timezone), the period 1st March 2022 00:00 to 1st April 2022 00:00 wouldn't have 31*24=744 hours, it only has 743 due to the time change. If the timezone was UTC on the other hand, this same period would have 744 hours.

  • Days. The number of whole days in the period. This automatically accounts for the configured Organization timezone, and automatically accommodates any clock adjustments such as daylight savings. Similarly to hours, it's not calendar days, but entire 24-hour periods (or in some cases, actually 23 or 25 hours if the period spans a Daylight Savings change)

    • For example: 25th March 2022 14:00 - 30th March 2022 14:00 would be 5 days (even though there are only 23 hours on 27th March due to the daylight savings clock adjustment).

    • Partial days are not counted, so 25th March 2022 14:00 - 30th March 2022 13:00 would only be 4 days (because it's an hour short of being a 5th full day).

  • Dates. the number of distinct calendar dates in the period in the configured Organization timezone. This is different to days:

    • For example: 25th March 2022 14:00 - 30th March 2022 13:00 would be 6 dates - 25th, 26th, 27th, 28th, 29th, and 30th. The time components don't matter here.

Supported Operators, Variables, Functions, and Type Casts

Standard mathematical syntax and operators are supported.

Operators

• Arithmetic Operators: + - * / ^

• Unary Operators: + -

• Precedence: ( )

• Equality: == != < > <= =>

• Modulo: %

• Ternary: ? :

• Logical AND: AND

• Logical OR: OR

Variables

• null

• true

• false

• Math.PI

Functions

• Math.max() Supports 2 to many arguments.

• Math.min() Supports 2 to many arguments.

• Math.floor() Supports 1 argument - returns the greatest integer less than or equal to the argument.

• Math.ceil() Supports 1 argument - returns the least integer greater than or equal to the argument.

• Math.round() Supports 1 argument - returns the nearest integer to the argument. Note that half-number values are rounded up.

  • For example, Math.round(2.5) returns 3.

• Math.pow() Exactly two arguments - returns the value of the first raised to the power of the second.

• Math.abs() Supports 1 argument - returns the absolute value of a number.

  • For example, Math.abs(-2) returns 2.

• isnull() Exactly two arguments - returns 1st argument if it's not null; 2nd argument if it is null - same as in SQL.

Type Casts

• String() Supports 1 argument.

• Number() Supports 1 argument.

• Boolean() Supports 1 argument.

String Functions

• String.left() Supports 2 arguments - 1 string and 1 numeric. Returns a string containing a specified number of characters from the left side of a string.

  • For example: String.left("Test", 2) will return "Te"

• String.right() Supports 2 arguments - 1 string and 1 numeric. Returns a string containing a specified number of characters from the right side of a string.

  • For example: String.right("Test", 2) will return "st"

• String.upper() Supports 1 string argument. Returns a string of all uppercase characters.

  • For example: String.upper("Test") will return "TEST"

• String.lower() Supports 1 string argument. Returns a string of all lowercase characters.

  • For example: String.lower("Test") will return "test"

• String.replace() Supports 3 string arguments. Returns a new string in which all occurrences of a specified character or string in the current string are replaced with another specified character or string.

  • For example: String.replace("Test replace", "replace", "worked") will return "Test worked"

• String.length() Supports 1 string argument. Returns a numeric count of all characters in a string.

  • For example: String.length("Test") will return 4

• String.substring() Supports 2 or 3 arguments.

  • If 2 arguments, the substring starts at a specified character position and continues to the end of the string.

    • For example: String.substring("Test", 1) will return ”est"

  • If 3 arguments, the substring starts at a specified start index and continues to the specified end index.

    • For example: String.substring("Test", 1, 3) will return ”es"

• String.contains() Supports 2 string arguments. Returns a boolean result if the first string contains the second string.

  • For example: String.contains("Test", "es") will return true

• String.indexOf() Supports 2 or 3 arguments.

  • If 2 arguments, returns the zero-based index of the first occurrence of a specified character or string within the first string. The method returns -1 if the character or string is not found in this instance.

    • For Example: String.indexOf("Test", "e") will return 1

  • If 3 arguments, returns the zero-based index of the first occurrence of a specified character or string within the first string, starting from the specified index. The method returns -1 if the character or string is not found in this instance.

    • For example: String.indexOf("test", "t", 1) will return 3

• String.trim() Supports 1 string argument. Returns a new string in which all leading and trailing spaces from the current string are removed.

  • For example: String.trim(" Test ") will return "Test"

Next: Plans and Pricing

Additional Support