Example of Calculation Syntax for MCE

Value Calculator Template Functions 

These calculations can use either the original legacy simplified naming convention or the MCE Item DataItem naming convention. For simplicity, all these examples use the simplified legacy naming convention.

Single Input Functions

Function Description Example
Single Value Functions Determine the single point aggregate value of the specified DataItem Data Point List at the specified time
rawdata(dataItemName, today) Returns the raw data value for the relevant dataItemName rawdata(12345.today)
data(dataItemName, today) Returns the calculated data value for the relevant dataItemName data(12345,today)
target(dataItemName, today) Returns the target value for the relevant dataItemName target(12345,today)
cost(dataItemName, today) Returns the cost value for the relevant dataItemName cost(12345,today)
targetcost(dataItemName, today) Returns the target cost value for the relevant dataItemName targetcost(12345, today)

DataSet List Funtions

Function Description Example
DataSet Functions Provides a time range of Data Points from the specified Data Item Data Point List
rawdataset(dataItemName, startTime, endTime) Returns the mean of the rawdataset between the relevant start and end times mean(rawdataset(12345, today-3, today))
dataset(dataItemName, startTime, endTime) Returns the mean of the calculated dataset between the relevant start and end times mean(dataset(12345, today-3, today))
targetset(dataItemName, startTime, endTime) Returns the mean of the target dataset between the relevant start and end times mean(targetset(12345, today-3, today))
costset(dataItemName, startTime, endTime) Returns the mean of the target dataset between the relevant start and end times mean(costset(12345, today-3, today))
lowcalcset(dataItemName, startTime, endTime) Returns the mean of the low calc dataset between the relevant start and end times mean(lowcalcset(12345, today-3, today))
highcalcset(dataItemName, startTime, endTime) Returns the mean of the high calc dataset between the relevant start and end times mean(highcalcset(12345, today-3, today))
DataSet Functions referencing period as well as today Returns the value of the dataset but using aggregation periods as well as date. This allows us to specify the average of, say the last the last 7 aggregation periods for a meter mean(dataset(12345, today – (7 * period(12345)), today – period(12345)))
Dataset functions using period in a date range Returns the dataset function of the data for the period in the day defined between the two period ranges. If meter 12345 has 30 min aggregations then 48 periods in day so this is averaging data for tomorrow (today+1) – 46 periods takes this 1:00 am today to period 38 which is 5.00 am today mean(dataset(12345, today+1-period(12345)*46, today+1-period(12345)*37)).
Remove specific values from a dataset Excludes the second value from a dataset defined by the 1st value mean(Remove(dataset(12345, today, today+1), 0)) This will calculate a mean of the dataset for meter 12345 excluding any values of 0. Note – can exclude multiple values by seperating with a comma so mean( Remove(dataset(12345, today, today+1), 0,10,99)) will calculate the mean excluding any values of 0, 10 or 99
Remove calculated values from a dataset Excludes the values from another calculation in the dataset defined by the 1st value sum(Remove(dataset(12345, today-1,dataset(23456, today-1, today))) This calculates the sum of dataset for meter 12345 excluding any values that are found in the meter 23456
RemoveGET (v161+) Excludes all values greater or equal to the value passed mean(RemoveGET(dataset(12345, today–7, today-1),100)) Removes all values greater than or equal to 100
RemoveLET (v161+) Excludes all values lower or equal to the value passed mean(RemoveLET(dataset(12345, today–7, today-1),0)) Removes all value less than or equal to 0

Conditional Statements

Function Description Example
choose(condition, trueValue, falseValue) Conditional statement returning the false or true value dependent on the logical result of evaluating the specified condition. choose(data(12345, today)>X, 10, 100)
and(condition1,condition2) Conditional statement returning the true or false value depending on the logical result of evaluating the specified conditions choose(3<4&&5<6, 1, 0)
or(condition1,condition2) Conditional statement returning the true or false value depending on the logical result of evaluating the specified conditions choose(3<4||5<6, 1, 0)
Pow(value, power) Value raised to the specified power Pow(data(12345,today),2)
choose((StartTime > dateserial(yyyy,mm,dd)), truevalue,falsevalue) Conditional statement returning the false or true value dependant on the logical result of the dateserial condition. choose((today >= dateserial(2010,01,05)), 10.0, 1.0)
choose((StartTime > dateserial(yyyy,mm,dd)) && (StartTime > dateserial(yyyy,mm,dd)), truevalue, falsevalue) Returns the value of the false or true value dependant on the logical value of the And statement choose((today >= dateserial(2010,01,05)) && (today < dateserial(2010,01,07)), 10.0, 1.0)
valueattime Allows you to apply different values at different times of the day. Example will enter value of 10 between 06:00 and 21:00 and a value of 20 outside these times valueattime(timeserial(06,0,0), timeserial(21,0,0),10,20)

Statistical and Misc Functions

Function Description Example
stddev(dataItemName) Standard deviation of the specified Data Item Data Point List. stddev(rawdataset(12345,today-3,today))
mean(dataItemName) Mean value of the specified Data Item Data Point List mean(rawdataset(12345,today-3,today))
median(dataItemName) Median value of the specified Data Item Data Point List median(dataset(12345,today-3, today))
min(dataItemName) Minimum value of the specified Data Item Data Point List min(costset(12345,today-3, today))
max(dataItemName) Maximum value of the specified Data Item Data Point List max(lowcalcset(12345,today-3, today))
sum(dataItemName) Summation of the specified Data Item Data Point List sum(highcalcset(12345,today-3,today))
EWMA(dataItemName, alpha value) Returns the Exponential Weighted Moving Average value of the dataItemName using the specified alpha value which MUST be between 0 and 1 EWMA(dataset(12345), 0.5)
cum(dataItemName, dateString) Returns the cumulative total of the specified meter from the date entered. Partial dates/times are allowed, leave that part blank ie 2012/04/01/00/00 could be /04/01/00/00 to do 1st april every year, or ///06/00 would reset the counter everyday at 6am cum([ItemName|CALC], “/04/01/00/00”) gives cumulative total of meter which resets on 1st april each year. You can also use the meterid in place of the ItemName ie cum([12345|CALC], “/04/01/00/00”) would work
Lookup(string, value) Returns the lookup value given the lookup specified by name and input value. String can be: FullName, FullMeterName, Company, Site, ItemName, SubType. This performs a lookup in the StandardValueCalcualatorTemplate and gives the flexibility to define multiple lookups throughout the database structure. Lookup(Company, 3) will return the 3rd value from the Lookup that is named as per the relevant Company. Lookup(FullName, data(23456,today)) will place the value from meter 23456 into the Lookup called Company|Site|Meter|CALC i.e. the full Itemname

MCE format calculations - these use the MCE Item DataItem naming convention

The MCE format to define an ItemName is completely different to the legacy IPMS but essentially the MCE format takes the format [CompanyName|SiteName|MeterName|CALC].XXXX where XXX is the command. The MCE format for Date and Time uses the principle of Ticks where a single Tick represents one ten millionth of a second. There are 10,000 ticks in a millisecond.
This list is not exhaustive in any way and ther are plenty of additional MCE functions that have not been included.
Function Description Example
period(dataItemName) Returns the aggregation period of the meter in ticks period(12345)
Time Returns the value of the date in ticks but Item MUST have an Start and End Time associated with it otherwise the data will not be calculated Time
Index Returns the Index of the value in the database with the first reading in the database having a value of 0. Very useful for initiating counts that are to be used with the .GetValue command but Item MUST have an Start and End Time associated with it otherwise the data will not be calculated Index
Index-value Returns the Index of the value in the database with the first reading in the database having a value of 0 less the value shown. Very useful for initiating counts that are to be used with the .GetValue command but Item MUST have an Start and End Time associated with it otherwise the data will not be calculated Index-30
MCEdataItemName
.Last.Value
Gets the last value for the Item in the database [Meniscus|Brewery|Inlet flow|Calc].Last.Value
MCEdataItemName
.EndTime
Gets the last time for the meter in the database in ticks [Meniscus|Brewery|Inlet flow|Calc].EndTime
MCEdataItemName
.Count
Counts the total number of points in the database [Meniscus|Brewery|Inlet flow|Calc].Count
(MCEdataItemName
.Count) – Index
Counts total number of readings and then subtracts the index from this. Creates a declining index counting to 1 at the most recent value in the database ([Meniscus|Brewery|Inlet flow|Calc].Count)-Index
choose(Index < MCEdataItemName .Count-30 ,0,1) If the current Index of the Item < than the total number of points in the Item -30 then 0 else 1 choose(Index < [Meniscus|Brewery|Inlet flow|Calc].Count-30,0,1)
MCEdataItemName
.GetValueAtTime( Time)
This acts in the same way as the IPMS format data(12345,today) and is the MCE syntax version of this. Except it interpolates between readings if the aggregation period is different [Meniscus|Brewery|Inlet flow|Calc] .GetValueAtTime(Time)
MCEdataItemName
.GetValueAtTime( dateserial( yyyy,mm,dd))
Gets the value at the date specified in the dateserial expression Meniscus|Brewery|Inlet flow|Calc] .GetValueAtTime( dateserial(2010,03,25))
MCEdataItemName
.GetValueAtTime( dateserial(new DateTime (today).Year, month(today), day(today))
Gets the value of the MCE DataItem at the day, month and year of the calculation as it is processed. Useful for taking a value for a daily aggregated data item and passing to, say, an hourly aggregated DataItem. There is currently no year(today) syntax and the new DateTime(today).Year is the c# equivalent to achieve this MCEdataItemName .GetValueAtTime( dateserial(new DateTime(today).Year, month(today),day(today)))
MCEdataItemName
.GetValueAtTime.(DateTime.Now
.Ticks)
Gets the value for just today’s exact date and time. Will interpolate between points on a linear basis if no exact point is found [Meniscus|Brewery|Inlet flow|Calc] .GetValueAtTime.(DateTime.Now.Ticks)
MCEdataItemName
.GetValueAtTime( dateserial( DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day))
Gets the value for the Item using today’s actual Year, Month and Day values. If we use Now.Year-1 this would use data for Last year, Month-1 would be last month etc. Meniscus|Brewery|Inlet flow|Calc].GetValueAtTime( dateserial(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day))
MCEdataItemName1
.GetItem( MCEdataItemName2
.Count-1).Value
Gets the last value from Item1 using the counter in Item2 [Meniscus|Brewery|Inlet flow|Calc].GetItem( [Meniscus|Brewery|Inlet flow|Calc].Count-1).Value
MCEdataItemName1
.GetItem( MCEdataItemName2
.Count-30).Value
Gets the value from Item1 using the counter in Item2 less 30 [Meniscus|Brewery|Inlet flow|Calc].GetItem( [Meniscus|Brewery|Inlet flow|Calc].Count-30).Value
MCEdataItemName1
.GetItem( (int)data(meterid2, today) +offset).Value
Gets the value from a Itemcalled Itemid2 and adds an offset of 48. This value is then used to get the value from the RawData of MCEDataItemName1. This is creating a calculated offset from a rawdata list. Can feed in the offset from a Lookup and the use this method to get the rawdata values [Profile|RAW].GetItem( (int)data(counter,today-period(counter)*1)+48).Value
counter( [MCEdataItemName|
CALC] .GetItem( Index).Time, true/false, period
Creates a counter that counts forward if the boolean value is set to true and counts backwards if set to false. Counts forward the number of aggregation periods defined in the period element.