New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Resource Advisor - Performance Analytics Forum
Showing results for 
Search instead for 
Did you mean: 

Making a 'Year to date Energy' measurement with an expression

Tl;dr: Make a measurement with agg method 'auto' called 'YTD Energy' (or whatever you like) and put this in for the default expression:


[[QueryPeriodCompliance=>NoIntervalCanSpanEndPoints]] (1 * [[QueryPeriodCompliance=>FirstIntervalCanSpanStart]] CUSUM PER YEAR ([Electricity:ENERGY][kWh]))

The story of where this comes from:


The 'CUSUM' function in calculated expressions keeps a running sum of its argument, and resets periodically according to the period you provide.


For example,


will give a running total of the energy consumed in the current month, making a 'sawtooth' graph that resets on the first of every month.




If you do CUSUM PER YEAR, you get a sawtooth that resets to zero every January 1.  If you view that measurement for the current week though, it only starts accumulating at the start of the query period and you don't see effect of the energy consumed from the start of the year.


There is an option on expressions that controls whether or not they can reach outside of the requested query time range, called 'QueryPeriodCompliance'.  Its purpose is to let you control what happens when you ask for something like "SUM PER MONTH()' but are only looking at a chart for the current day - should data for the whole month be used, even though you said you only want output for the day?


QueryPeriodCompliance has four allowed values:

  • FirstIntervalCanSpanStart
  • LastIntervalCanSpanEnd
  • NoIntervalCanSpanEndPoints
  • BothIntervalsCanSpanEndPoints


The default is 'NoIntervalCanSpanEndPoints' and that's what is preventing an annual CUSUM from going back to the start of the year.  The expression:


[[QueryPeriodCompliance:FirstIntervalCanSpanStart]] CUSUM PER YEAR([Electricity:ENERGY])


almost gets what we want, but it goes too far - it will output values starting from the beginning of the year no matter what the query period is.


The trick is to use QueryPeriodCompliance a second time to trim the output after the CUSUM has finished adding up the data from outside the query period.  The most natural thing to try is to just wrap the above expression in another directive, but here's where the twist comes in.  Directives of this kind (called 'calculation policy directives') only apply to operators, so we have to insert an additional do-nothing operation on our result so there is something to hang the policy on.  I picked multiplying by 1, but you could add zero and get the same effect.  Don't worry about this part too much, but the end result is:


[[QueryPeriodCompliance=>NoIntervalCanSpanEndPoints]] (1 * [[QueryPeriodCompliance=>FirstIntervalCanSpanStart]] CUSUM PER YEAR ([Electricity:ENERGY][kWh]))


If you create a measurement 'YTD Energy', put that formula in for the  default expression, and mark the measurement's aggregation type as 'Auto', you'll get the energy used since the start of the current year, no matter what your query period is.  It will aggregate normally using all of the periods listed in the trend card.


It's important to make the aggregation method for the measurement 'auto' as described here:


Here is the above expression evaluated for the first week of June - the value each day is the total energy used since the start of the year up until the end of that day:


YTD Energy.PNG