There is a client who has a reporting issue problem. Let’s call him Ray. His annual sales are to be split into summer and winter sales, but on a seasonal, not calendar basis. In Ray’s world, winter begins in November and ends in April spanning two calendar years. While Ray sounded calm when he called me, I could feel the tear of frustration welling up in his eyes. How can we summarize annual winter sales in MetrixND when the sales fall in two calendar years?
Let’s be more specific. The picture to the right shows the monthly sales. Ray wants to call November 1995 through April 1996 the Winter 1995 sales.
Typically, the MetrixND’s SumAcross function is used to convert monthly data to annual total. The process takes two steps. First, the monthly sales are split into calendar summer and winter sales in a monthly transformation table. Second, the annual sums are calculated using the SumAcross function in an annual transformation table. The steps, transformations, and results are show below.
But, this is not what Ray wants to do. To use MetrixND’s data transformation capabilities, Ray needs to move the January 1996 through April 1996 values into the January 1995 through April 1995 positions as show below. If Ray can do this, then the annual transformation technique works.
The good news is that Ray called and I have a solution.
Using the following transform, I can move January to April sales using the Lead function.
Once I move the data, I can use the SumAcross function, just like before, to summarize annual summer and winter sales leaving Ray very happy.
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.
