UPDATE (Sep 2, 2015): Thanks to Frank for the following suggestion. There’s a built-in WebFOCUS function called: DATEMOV
Here’s the code snippet: DATEMOV(DATE,’EOM’) (or EOQ, EOY, EOW)
Official documentation on this can be found here.
However, here’s the old technique that will teach you about other built-in functions available.
/ UPDATE (Sep 2, 2015)
If your time dimension is missing a “last day of month” field, you can easily calculate it. Here’s the pseudocode:
- Get the current date
- Take the year and the month and create the first date of the month
- Add 1 month
- Minus 1 day
- The result will get you the last day of the month
Here’s the WebFOCUS code. I’ve broken it up into more than one define field to make it easier to read. I have a master file called DIM_DATE where CAL_DATE is the date we want to figure out what the last day of its month is:
DEFINE FILE DIM_DATE
DATE_STR/A20 = FPRINT(CAL_DATE,’YYMD’,’A10′)|’ 0:00am’;
DATE_TIME/HYYMDS = HINPUT(20,DATE_STR,20,’HYYMDS’);
MONTH_STR/A2 = EDIT(HPART(DATE_TIME,’MONTH’,’I2′));
YEAR_STR/A4 = EDIT(HPART(DATE_TIME,’YEAR’,’I4′));
START_OF_MONTH_STR/A10 = YEAR_STR|’-’|MONTH_STR|’-01′;
START_OF_MONTH/YYMD = HDATE(HINPUT(10,START_OF_MONTH_STR,10,’HYYMDS’),’YYMD’);
NEXT_MONTH/YYMD = DATEADD(START_OF_MONTH,’M’,1);
LAST_DATE_OF_MONTH/YYMD = DATEADD(NEXT_MONTH,’D’,-1);
Of course, you can combine this into one big statement, but that would make it hard to read. I’ve also provided a user function so you can reuse it anywhere. You can download it here.
For more date functions, check out this link: