Since dates must be supplied in the advanced math toolbox function arguments, Math Toolbox supports several date manipulation funcitons. The "date literal" functionality has been expanded. In addition to all the existing features, you can now specify offsets to the current date being calculated. In the day, month or year fields, if you specify an unsigned number then that number is used as the absolute day, month or year. If you specify a number with a + or - sign in front of it, then it indicates the number of days, months or years to offset from the current value. You can use any combination of signed or unsigned number for each field:
| D |
Current day being calculated |
| #MM/DD/YY |
Current date being calculated |
| #MM/LL |
Last day of the current month being calculated |
| #1/1 |
January 1 of the current year being calculated |
| #+1/1 |
The first of next month from the date being calculated |
| #+1/LL |
The last day of next month from the current date being caluclated |
| #1/1/+1 |
January 1 of next year from the date being calculated being calculated |
| #-1/LL |
The last day of the previous month from the date being calculated |
| #MM/-7 |
Date seven days ago from the current date being calculated |
| BOM |
Beginning of the current Month being calculated |
| EOM |
End of the current month (i.e. last day of month) |
| BOQ |
Beginning of the current Quarter being calculated |
| EOQ |
End of the current Quarter being calculated |
| EOD |
End of current day being calculated (23:59:59) |
| #MM/DD/YY/HH/NN |
Current date hour (HH) and minutes (NN) of current date/slot being calculated. Used primarily when calculating daily detail results |
| WEEKSTART(N) |
Start Date of the week definition N for the current date/slot being calculated. |
| WEEKEND(N) |
End Date of the week definition N. (The end date is only defined when the current date being calculated is the date on which Week definition N would normally bring, otherwise WeekEnd is NoDate) |
NOTE: If the current date being calculated is March 25, 2003, then #-1/DD means February 25, 2003. If the current date being calculated is March 30, 2003, then #-1/DD means February 28, 2003. (If you actually wanted thirty days from the first of the previous month, you would use #-1/1 +30)
EXAMPLE #1: Calculate the average of an hourly variable from the 1st of the month thru 1PM of each day
GETAVG(C1,BOM TO #MM/DD/YY/13/NN,BYHOUR)
When calculating Jan 2nd,2015 into a daily variable this would calc from 1/1/2015 00:00 thru the 1/2/2015 13:00 Slot (i.e. data for the 1PM hour will be included in the calc).
EXAMPLE #2: Calculate the 7 day moving avearage from an hourly variable into a daily calculation.
GETAVG(C1,#MM/-6 TO EOD, BYHOUR)
When calculating Jan 7th, 2015 this calc would include data from Jan 1st 00:00 thru the last hourly slot of Jan 7th, 2015 (i.e. data for Jan 7th, 11PM slot will be included).
EXAMPLE #3: Calculate the 12 Month Average on the last day of the month
Other Date Functions:
| NoDate |
Equivalent to a blank date value |
| Month (Date) |
Month number, 1-12, of the date (i.e., Month(#5/1)=5) |
| Day (Date) |
Day of the month, 1-31 (i.e., Date(#1/LL) = 31) |
| Year (Date) |
Year of the provided date (i.e., Year(#1/1/2008) = 2008 |
| Weekday (Date) |
Weekday number of Date (i.e., Weekday(#1/1/2001) = 2 (Monday)
- = Sunday
- = Monday
- = Tuesday
- = Wednesday
- = Thursday
- = Friday
- = Saturday
|
| TodayIF(condition) |
Returns Current Date being calculated when condition is True, else NoDate when false
short form for the expression IF(condition, D, Nodate) |
|
|