Formula Cheat Sheet
- Former user (Deleted)
- Michael Addison
- Former user (Deleted)
The purpose of this document is to act as a cheat sheet for the various formula short-codes. This is not intended as an exhaustive list of everything that can be done with formulas in Plant Focus.
| Short-Code | Valid Datatypes | Description |
|---|---|---|
| # | All | If a number is a zero but the formula is followed by the # symbolt it will be replaced with a 1 instead. |
| a | Once A Day, RealTime, Once A Month, Once a Shift, Optimization, LAB | Average of all values including zeros. |
| A | Once A Day, RealTime, Once A Month, Once a Shift | The time weighted average of all datapoints for the day (including zeros) |
| b | Once A Day, RealTime, Once A Month, Once a Shift, Optimization, LAB | Average of all values excluding zeros. |
| B | Once A Day, RealTime, Once A Month, Once a Shift | The time weighted average of all datapoints for the day excluding zeros) |
| c | Once A Day, RealTime, Once A Month, Once a Shift | Calculated value instead of raw value (including adjustment) |
| d | RealTime | The difference between the 2 most recent datapoints. |
| e | Once A Day, RealTime | Duration of time from NOW to the end of the selected timeframe or shift. |
| f | Once A Day, RealTime, Once A Month, Once a Shift | Performs the statistical function (STDEV, MIN, MAX) that appears after f and before the PLCid. Example, fSTDEV6120 is the Standard Deviation of DOR Item 6120, including zeroes. |
| F | Once A Day, RealTime, Once A Month, Once a Shift | Performs the statistical function (STDEV, MIN, MAX) that appears after F and before the PLCid. Example, FSTDEV6120 is the Standard Deviation of DOR Item 6120, NOT including zeroes. |
| i | Once A Day | Most recent inventory running total (including adjustment) |
| k | RealTime, Once a Shift | The very last datapoint of the previous day, based on the start date selected. |
| l | Once A Day, RealTime, Once a Shift | Last value present prior to end date, regardless of start date. Only raw value will be returned, or only adjustment, if present, will be returned. |
| L | RealTime, Once a Shift | The next to last datapoint of the timeframe specified based on the start and end dates selected. If no data is present within these times then no data will be returned |
| m | Once A Day, RealTime, Once A Month, Once a Shift, Optimization, LAB, Silo Inventory | Most recent value present prior to end date. Inclusive of data on the end date. Includes adjustments. |
| M | RealTime, Once a Shift | The value directly before the most recent value prior to the end date (independent of start time). If used to reference a daily datapoint it will include the adjustment. |
| n | Once A Day, Once A Month, Once a Shift | Sum of values, does not include adjustments. |
| o | Once A Day, Once A Month, Once a Shift | Offset or adjustment value only. |
| p | Once A Day, RealTime, Once A Month, Once a Shift, Optimization, LAB, Downtime, Silo Inventory | Current day's value (including adjustment) |
| q | Once A Day, RealTime, Once A Month, Once a Shift, Optimization, LAB | Previous day's value. (On DOR report, cannot be a manual entry) |
| r | Once A Day | Reference value only valid on DOR admin for automated data gathering, NOT reporting, will return 0 otherwise. Valid for IP21 only. |
| R | Once A Day, RealTime, Once A Month, Once a Shift, Recipe | ComponentID of the recipe system. |
| s | Once A Day, RealTime, Once A Month, Once a Shift | The sum of InputID from shift comments |
| S | Once A Day, RealTime, Once A Month, Once a Shift | The average of InputID from shift comments |
| t | Once A Day, RealTime, Once a Shift | Duration of time from selected start date to end date. If selected end date is now then it will be the duration of time from start to now in minutes. |
| T | Once A Day, RealTime, Once a Shift | Duration of time from selected start date to end date. If selected end date is now then it will be the duration of time from start to end date. |
| u | Once A Day, RealTime, Downtime | Duration of time equipment was operating between start date and end date. If the selected end date is now it will be the duration of selected start date to now. |
| U | Once A Day, RealTime, Downtime | Duration of time equipment was NOT operating between start and end date. If selected end date is now it will be the duration of selected start date to now. |
| v | RealTime | Duration of time from the very last datapoint present in that item's data to now in seconds |
| V | RealTime | Duration of time from the very last datapoint present in that item's data to the preceding data point in seconds |
| z | Once A Day, RealTime, Once A Month | The average of all datapoints for the day excluding zeros |
| Z | Once A Day, RealTime, Once A Month | The time weighted average of all datapoints for the day excluding zeros. |
| dow | Date Labels | Returns the day of the week of the end date given DOW for date returns 'Monday' or 'Tuesday' |
| DOW | Date Labels | Returns the day of the week of the start date given dow for date returns 'Monday' or 'Tuesday' |
| date | Date Labels | This will return the text representing the end date (MM/dd/yyyy) |
| DATE | Date Labels | This will return the text representing the start date (MM/dd/yyyy) |
| datetime | Date Labels | Returns the text representing the end date (MM/dd/yyyy HH:mm) |
| DATETIME | Date Labels | Returns the text representing the start date (MM/dd/yyyy HH:mm) |
| DATEFORMAT,HH:mm | Date Labels | Returns the start date formatted the way you specify after the comma |
| dateformat,MM/dd/yyyy HH:mm | Date Labels | Returns the end date formatted the way you specify after the comma |
| DATEFORMATM,###,mm/dd/yyyy | Date Labels | Returns the most recent timestamp of data before the start date formatted like after the comma . |
| dateformatm,###,mm/dd/yyyy | Date Labels | Returns the most recent timestamp of data before the end date formatted like after the comma . |
| NOW | Date Labels | Returns the current date time of the site in localized time format |
| NOW,HH:mm | Date Labels | Returns the current time of the site formatted the way you specify after the comma. |
Real Time
When using 'R' it can be used to reference Text or Numbers. When referencing Text it may not be used in a 'Display Formula', IE: c417 because the calculated results of all PLCid's must be numeric
When using 'R' in a DOR report, you can specify items that represent Text. When doing so you must specify the PLCid whose raw data value is the currently selected RecipeID
IE: R5;6,1 meaning CategoryID=5, ComponentID=6, PLCid=1's value for the report end date will be the RecipeID so we have the 3 items required to lookup recipe value.
When entering a formula you can manually override what RecipeID is chosen
IE: R5;6;1 meaning CategoryID=5, ComponentID=6, RecipeID=1 (If you specify a ,1 at the end it will ignore it)
Optimization Items
Specifier: ReturnVal
Daily Avg: 1
Daily Dev: 2
7day Avg: 3
7Day Dev: 4
30Day Avg: 5
30Day Dev: 6
Min val: 7
Max val: 8
PctLow: 9
PctHigh: 10
Upper Limit: 11
Lower Limit: 12
target: 13
Examples:
After this, you can manually specify a Recipe if so desired, do this this place a comma and seperate the recipeID's with semicolons.
Examples:
OPTa5,1
Signifies this is for the Optimization system, 'a' is just like the DOR data so we're getting the DatapointWeightedAverage, its OPTid 5, and we want the data for '1' (DailyAvg)
Note no recipe specified so it will ignore recipe specific information and return avg of all values over timeframe specified
OPTb5,1,10
Signifies this is for the Optimization system, 'b' is just like the DOR data so we're getting the DatapointWeightedAverageWithoutZeros, its OPTid 5, and we want the data for '1' (DailyAvg), and data only for recipeid 10.
OPTb5,1,10;11;12
Signifies this is for the Optimization system, 'b' is just like the DOR data so we're getting the DatapointWeightedAverageWithoutZeros, its OPTid 5, and we want the data for '1' (DailyAvg), and data for recipeid's 10,11,12.
LAB Items inported within the DOR Formula
Specifier: ReturnVal
DataValue: 1
Low Low Target: 3
Low Target: 4
Target: 5
High Target: 6
High High Target: 7
Examples:
After the 3 character LAB and the function to perform, you place the LABid
this is a completed function as of now, you can continue to customize with these options
place a comma and a number to indicate what information you want to display.
After this, you can manually specify a Recipe if so desired, do this this place a comma and seperate the recipeID's with semicolons.
Examples:
LABa5,1
Signifies this is for the LAB system, 'a' is just like the DOR data so we're getting the Datapoint Weighted Average, its LABid 5, and we want the data for '1' (DataValues)
Note no recipe specified so it will ignore recipe specific information and return avg of all values over timeframe specified
LABb5,1,10
Signifies this is for the LAB system, 'b' is just like the DOR data so we're getting the DatapointWeightedAverageWithoutZeros, its LABid 5, and we want the data for '1' (DataValues), and data only for recipeid 10.
LABb5,1,10;11;12
Signifies this is for the LAB system, 'b' is just like the DOR data so we're getting the DatapointWeightedAverageWithoutZeros, its LABid 5, and we want the data for '1' (DataValues), and data for recipeid's 10,11,12."
Downtime Items imported within DOR Formula
Specifier: ReturnVal
Circumstance: c
Scheduled: s
Incident: i
Detail code to be specified next: d
Specific Cause to be specified next: C
Specific Equipment to be specified next: E
Specific detail category to be specified next: D
Count As Stop: x
Count as Stop equals False: X
Examples:
After the 3 character DWN and the function to perform, you place the Downtime Area Number
After the formula is completed, place a comma and a number to indicate what information you want to display.
If no comma is present, it will return all downtime counts/minutes for the selected area instead of limiting to the following.
After the comma, these are your options (can use multiple read below):
c =Circumstance
s =Scheduled
i =Incident
d =Detail Code specified on each downtime event
C =This Specific Cause
E =This specific Equipment
D =This specific DetailCategory (available categories are in table: tblAreaShutdown_DetailCategories, each DetailCode is a member of a DetailCategory)
x =Include when this item is an Equipment Stop event. The Count As Stop=True on the data entry page
X =Include when this item is NOT an Equipment Stop event. The Count As Stop=FALSE on the data entry page
After this you indicate the details of each if it has any seperated by semicolons
Letters d,C,E can be used with semicolons, c,s,i cannot be.
When multiple instances are specified seperated by commas it is interperated as an AND statement meaning all cases need to be true when it gets the data
Examples:
DWNp10 This will return the total number of downtime occurrences that happened within the timeframe specified for Area 10.
DWNU10 This will return the total duration of downtime that happened within the timeframe specified for Area 10.
DWNp10,c This will return total number of circumstance events that began within timeframe specified for Area 10
DWNp10,dPI;MI This will return the total number of downtime occurrences that happened for area 10 within timeframe that were detail code PI or MI only
DWNp10,C44;45;46;47 This will return the total number of downtime occurrences that happened for area 10 within timeframe that were CAUSEID: 45 or 46 or 47
DWNp10,c,dPI;MI,C44;45;46;47 This will return total number of downtime occurrences that happened for area 10 within timeframe that were circumstances with Detail Code PI or MI and that were CAUSEID: 45 or 46 or 47.
Silo based data within DOR formula
Prefix formula with SILO the next letters can be:
p = return the value prior to the end date selected that matches the criteria exactly, if there is a record present but it does not match (for instance column0=1) it will return nothing or a 0
m = return the most recent value prior to the end date selected that matches criteria, will most likely always return a value and may not be most recent value!
The next characters are the identity of the silo we are specifying, f10r00 indicates Silo 1A
The next value is a Formula Specifier:
Tonnage Val="1"
Measured Height Val="2"
Max Height Val="3"
Max Tonnage Val="4"
Max-Measured=Filled Height Val="5"
Selected column INT Val="6"
Admin page is located at: ~/DesignerPages/area_designer_datesStandard.aspx
Examples:
SILOpf10r00,1
Signifies this is for the current value in the silo (p) for silo number 'f10r00', for the current TONNAGE (,1)
After this, you can manually specify a COLUMN and SELECTED VALUE if so desired, to do this place a comma and seperate the SELECTED VALUES with semicolons.
Silo Unique ID's are under the "Row Layout" selection and indicated by the "RowID" column.
SILOpf10r01,1,2;3;4
Silo number 2 (SILOpf10r01), Tonnage (,1) where COLUMN# 2 (,2) (column index starts at 0, this is the product MATERIAL TYPE, see image) values selected are either 3 or 4.(;3;4) (Cement T I/II or Reclaim Clinker see image 2)
Column Layout is designated from the "Column Layout" selection.
..tblArea_DatesCBOSpecial = 'Special' Column Info
..tblArea_DatesCBOWhatMill = 'Source' Column Info
..tblArea_DatesCBOType = 'Type' Column Info
..tblArea_DatesCBOReleased = 'Released' Column Info
....Within each of these options you'll see it appropriate ID for each option.
SILOpf10r02,2,0;1
Silo number 3 (SILOpf10r02), get measured height (,2), where column0 (,0) (Status or Special) = 1 (;1) (#1 Finish Mill)
SILOmf10r03,1,2;3
Silo number 4's most recent tonnage where its column 2 (Product Type) = 3 (Reclaim Clinker)
SILOpf10r09,6;0
Silo number 10's current value selected for the column 'Special'. It will return the INT of the value selected for the column following the ;