Back to Blog

Forecasting

Summing and Averaging

February 10, 2021

The Sum function in MetrixND seems like a complex way to make adding difficult.

In a MetrixND transformation, numbers are added by joining variables with the “+” sign. Adding three variables is as simple as writing the following expression in the transformation editor formula box:

DataSource.Variable1 + DataSource.Variable2 + DataSource.Variable3

The complex way to add is using the “Sum” function. This function requires inserting the three variables separated by commas (,) as Sum function parameters, as shown below:

Sum(DataSource.Variable1,DataSource.Variable2,DataSource.Variable3)

Technically speaking, the Sum function requires five extra characters to do the same work as the traditional “+” sign.

So, why does MetrixND include this function?

In the situation where variables have missing values, the calculation using the “+” sign results in a missing value. In other words, a number plus a missing value equals a missing value.

100 + MISSING = MISSING

While the Sum function behaves the same, the Ignore Missing option changes the behavior to produce a value. In other words, the Sum function with the Ignore Missing option selected means that a number plus a missing value equals a number.

100 + MISSING = 100

To activate the Ignore Missing option, check the Ignore Missing box in the transformation editor as shown below.

The Ignore Missing options works with the following functions:

  • Sum
  • Avg
  • Max
  • Min

It doesn’t matter whether you use traditional math operators or the functions when data is complete. However, when the dataset has missing values, the functions and Ignore Missing options may be the difference between forecasting a number and forecasting a MISSING.

Complexity has its purpose.

By Mark Quan

Principal Forecast Consultant

Mark Quan is a Principal Forecast Consultant with Itron’s Forecasting Division. Since joining Itron in 1997, Quan has specialized in both short-term and long-term energy forecasting solutions as well as load research projects. Quan has developed and implemented several automated forecasting systems to predict next day system demand, load profiles, and retail consumption for companies throughout the United States and Canada. Short-term forecasting solutions include systems for the Midwest Independent System Operator (MISO) and the California Independent System Operator (CAISO). Long-term forecasting solutions include developing and supporting the long-term forecasts of sales and customers for clients such as Dairyland Power and Omaha Public Power District. These forecasts include end-use information and demand-side management impacts in an econometric framework. Finally, Quan has been involved in implementing Load Research systems such as at Snohomish PUD. Prior to joining Itron, Quan worked in the gas, electric, and corporate functions at Pacific Gas and Electric Company (PG&E), where he was involved in industry restructuring, electric planning, and natural gas planning. Quan received an M.S. in Operations Research from Stanford University and a B.S. in Applied Mathematics from the University of California at Los Angeles.