## 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. |