Monthly Archives: April 2015

WebFOCUS: Calculating the last day of the month

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:

  1. Get the current date
  2. Take the year and the month and create the first date of the month
  3. Add 1 month
  4. Minus 1 day
  5. 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
-*convert the date to a string version of a date time to  take advantage of various WebFOCUS date functions
DATE_STR/A20 = FPRINT(CAL_DATE,’YYMD’,’A10′)|’ 0:00am’;

-* convert the string to a date time data type
DATE_TIME/HYYMDS = HINPUT(20,DATE_STR,20,’HYYMDS’);

-* get the month as a string
MONTH_STR/A2 = EDIT(HPART(DATE_TIME,’MONTH’,’I2′));

-* get the year as a string
YEAR_STR/A4 = EDIT(HPART(DATE_TIME,’YEAR’,’I4′));

-*a create the first date of the month string
START_OF_MONTH_STR/A10 = YEAR_STR|’-‘|MONTH_STR|’-01′;

-* convert start date of month to a date data type
START_OF_MONTH/YYMD = HDATE(HINPUT(10,START_OF_MONTH_STR,10,’HYYMDS’),’YYMD’);

-* add 1 month
NEXT_MONTH/YYMD = DATEADD(START_OF_MONTH,’M’,1);

-* subtract 1 day to get the last day of the month
LAST_DATE_OF_MONTH/YYMD = DATEADD(NEXT_MONTH,’D’,-1);

END

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. Download WebFOCUS get last date of month file

For more date functions, check out this link: