I have a report which i am trying to calculate the total number events for the current week. The end user wants to be able to just put one input date from the front end and it will calculate the week for that date the end user put in. For example, if the end user put in 03/04/09 i need to calculate all the # of events from 03/01/09 to 03/07/09. The end user wants the dates for the week to be from Sunday to Saturday.

Can someone please help me figure out this calculation?

Also, I need to figure out the previous week as well and the current month and current year.

Please Help!

This message has been edited. Last edited by: prodrigu,

Please do your homework, look into Standard Date Functions, "DATEMOV" in particular: "Moving a Date to a Significant Point".

EOM is the end of month.
BOM is the beginning of month.
EOQ is the end of quarter.
BOQ is the beginning of quarter.
EOY is the end of year.
BOY is the beginning of year.
EOW is the end of week.
BOW is the beginning of week.
NWD is the next weekday.
NBD is the next business day.
PWD is the prior weekday.
PBD is the prior business day.
WD- is a weekday or earlier.
BD- is a business day or earlier.
WD+ is a weekday or later.
BD+ is a business day or later.

You can also use the following in DM.. this will take a param called INPUT_DATE and calculate the previous sun to next sat.

Jodye

  

-SET &INPUT_DATE='20090317';
-SET &DOW=DOWK(&INPUT_DATE, 'A4');
-SET &LASDIFF = IF &DOW EQ 'SUN' THEN 0
-ELSE IF &DOW EQ 'MON' THEN -1
-ELSE IF &DOW EQ 'TUE' THEN -2
-ELSE IF &DOW EQ 'WED' THEN -3
-ELSE IF &DOW EQ 'THU' THEN -4
-ELSE IF &DOW EQ 'FRI' THEN -5
-ELSE IF &DOW EQ 'SAT' THEN -6;
-SET &LASTSUN = AYMD(&INPUT_DATE, &LASDIFF ,'I8');
-*-GET THE FOLLOWING SATURDAY
-SET &NEXTSAT = AYMD(&LASTSUN, 6 ,'I8');

-TYPE &LASTSUN
-TYPE &NEXTSAT

Come to think of it why do in many lines what you can do in two...

  
-SET &INPUT_DATE='20090317';
-SET &BOW = DATEMOV(&INPUT_DATE, 'BOW');
-SET &EOW = DATEMOV(&INPUT_DATE, 'EOW');
-TYPE &BOW
-TYPE &EOW

although strangely EOW is giving thursday the 19th. hmm...

Jodye,

When using Date Functions in Dialogue Manager I think they first have to be converted to date format using DATECVT. e.g.:

-SET &ECHO=ALL;

-DEFAULT &INPUT_DATE = '&YYMD.EVAL';

-SET &BOW1 =
-  DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 =
-  DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-TYPE &BOW1
-TYPE &EOW1

Though I'm not sure how you get a week's date range Monday to Sunday or Sunday to Saturday without having to add/subtract to/from the BOW and EOW results. Setting BUSDAYS to SMTWTFS doesn't do it.

-SET &ECHO=ALL;

-DEFAULT &INPUT_DATE = '&YYMD.EVAL';

-SET &BOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-SET &BOW = DATECVT( DATEADD(DATECVT(&BOW1,'I8YYMD','YYMD'),'D', -1), 'YYMD','I8YYMD');
-SET &EOW = DATECVT( DATEADD(DATECVT(&EOW1,'I8YYMD','YYMD'),'D', 1), 'YYMD','I8YYMD');

-TYPE &BOW1 &BOW
-TYPE &EOW1 &EOW

We're giving it away today.

Thanks Francis you just triggered a memory. I had trouble with that a long time ago (years) and just came up with the code I posted... which works perfectly and lets you easily define the week. Nice when you look at some old code and think "why did I do it like that?". If only there was time for line by line documentation inside the fex.

that is why I like reading this forum... I often find a better (read more efficient) way to do something that is already working.

Francis,

I used your code above and below is the output I am getting. How can I set the beginning of the week to Sunday and the end to Saturday? So the dates should be from 20090315 to 20090321.


-SET &ECHO=ALL;
-DEFAULT &INPUT_DATE = '20090317';
-SET &BOW1 = DATECVT((DATEMOV((DATECVT(20090317,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 = DATECVT((DATEMOV((DATECVT(20090317,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');
-SET &BOW = DATECVT( DATEADD(DATECVT(20090316,'I8YYMD','YYMD'),'D', -2), 'YYMD','I8YYMD');
-SET &EOW = DATECVT( DATEADD(DATECVT(20090320,'I8YYMD','YYMD'),'D', 2), 'YYMD','I8YYMD');

-TYPE 20090316 19001231
20090316 19001231
-TYPE 20090320 19001231
20090320 19001231
-EXIT

Why not use the following for your second two dates?

-SET &BOW = AYMD(&BOW1,-2,'I8YYMD');
-SET &EOW = AYMD(&EOW1,2,'I8YYMD');

T

This works for me:

-SET &ECHO=ALL;
-DEFAULT &INPUT_DATE = '20090317';

-*-- Determine Monday and Friday dates ---
-SET &BOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-*-- Determine Sunday and Saturday dates ---
-SET &BOW = DATECVT( DATEADD(DATECVT(&BOW1,'I8YYMD','YYMD'),'D', -1), 'YYMD','I8YYMD');
-SET &EOW = DATECVT( DATEADD(DATECVT(&EOW1,'I8YYMD','YYMD'),'D', 1), 'YYMD','I8YYMD');

-*-- Determine Sunday and Saturday dates - alternative ---
-SET &BOW2 = AYMD(&BOW1,-1,'I8YYMD');
-SET &EOW2 = AYMD(&EOW1,1,'I8YYMD');

-*-- Display dates ---
-TYPE &BOW1 &BOW &BOW2 
-TYPE &EOW1 &EOW &EOW2

A little more, display the day of week:

-SET &ECHO=ALL;
-DEFAULT &INPUT_DATE = '20090317';

-*-- DETERMINE MONDAY AND FRIDAY DATES ---
-SET &BOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-*-- DETERMINE SUNDAY AND SATURDAY DATES ---
-SET &BOW = DATECVT( DATEADD(DATECVT(&BOW1,'I8YYMD','YYMD'),'D', -1), 'YYMD','I8YYMD');
-SET &EOW = DATECVT( DATEADD(DATECVT(&EOW1,'I8YYMD','YYMD'),'D', 1), 'YYMD','I8YYMD');

-*-- DETERMINE SUNDAY AND SATURDAY DATES - ALTERNATIVE ---
-SET &BOW2 = AYMD(&BOW1,-1,'I8YYMD');
-SET &EOW2 = AYMD(&EOW1,1,'I8YYMD');

-*-- DETERMINE FRIENDLY DATES ---
-SET &BOW3 = DOWKL(&BOW, 'A12') || (', ' | CHGDAT('YYMD', 'MXDYY', &BOW, 'A17'));
-SET &EOW3 = DOWKL(&EOW, 'A12') || (', ' | CHGDAT('YYMD', 'MXDYY', &EOW, 'A17'));

-*-- DISPLAY DATES ---
-TYPE &BOW1 &BOW &BOW2 &BOW3
-TYPE &EOW1 &EOW &EOW2 &EOW3

quote:

-TYPE 20090316 19001231
20090316 19001231
-TYPE 20090320 19001231
20090320 19001231
-EXIT

The 1900 dates can be avoided by the use of .EVAL so change the &BOW1 to &BOW1.EVAL

I still do not now why, but it will help most of the time

Thanks Francis for all your help. The code you gave me worked. Now that you showed me how to calcualte the current week, how do you go about calculating the previous week and current month and current year though? Sorry for asking so many questions.

Thanks FrankDutch about letting me know about adding .EVAL to &BOW1.

quote:

EOY is the end of year.
BOY is the beginning of year.

You need the book " (almost) 1001 ways to work with dates in webfocus" isbn 978-0-9791722-1-2 it's worth the 25$.

The previous week would be:

First substract 7 days from the actual day and use that day to do the same calculations .

The actual month, change the BOW to BOM and EOW to EOM. Same with teh actual year.

(the previous month can be found by moving to the beginning of the month and substract 1 day, that moves you to the end of the previous month.

there are many more (almost 1001) ways to do this....

Thanks for all your help FrankDutch. I will look into buying that book. Does that book include how to convert all date formates all well?

I ask because the table field name format I am using is HYYMDS. And I keep getting this error which i believe i need to convert one of the formats to the other.

(FOC36346) INCORRECT USE OF DATE-TIME FIELD OR CONSTANT

Well,

It depends on what you mean by "previous week", "current month" and "current year".

If "previous week" means the Sunday and Saturday of the previous week, then this will do it:

-SET &ECHO=ALL;
-SET &INPUT_DATE = '20090317';

-*-- DETERMINE MONDAY AND FRIDAY DATES ---
-SET &BOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');
-SET &EOW1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-*-- DETERMINE SUNDAY AND SATURDAY DATES ---
-SET &BOW2 = AYMD(&BOW1,-1,'I8YYMD');
-SET &EOW2 = AYMD(&EOW1,1,'I8YYMD');

-*-- DETERMINE PREVIOUS SUNDAY AND SATURDAY DATES ---
-SET &BOWP = AYMD(&BOW2,-7,'I8YYMD');
-SET &EOWP = AYMD(&EOW2,-7,'I8YYMD');

-*-- DETERMINE FRIENDLY DATES ---
-SET &BOW3 = DOWKL(&BOW2, 'A12') || (', ' | CHGDAT('YYMD', 'MXDYY', &BOW2, 'A17'));
-SET &EOW3 = DOWKL(&EOW2, 'A12') || (', ' | CHGDAT('YYMD', 'MXDYY', &EOW2, 'A17'));

-*-- DISPLAY DATES ---
-TYPE &BOW1 &BOW2 &BOW3
-TYPE &EOW1 &EOW2 &EOW3

-TYPE PREVIOUS &BOWP - &EOWP

We've been led up the Dialogue Manager garden path.

You want to do this in a DEFINE or COMPUTE with a Date-Time data column? I assumed that because the user was inputting a Dialogue Manager variable which contains the date, you wanted the From and To dates in Dialogue Manager as well, to be used as filters in your program.

DEFINEs and COMPUTEs with Date-Time columns are done a little differently.

Yes, when I say previous week it will be the previous Saturdat to Sunday. And when I say current month I mean for this whole month. If the end user puts todays date on the front end he wants to see everything for that month. And current year follows as well if he puts in todays date it should give him everything in that current year so it should give him everything in 2009. Do i make sense or no? Let me know.

The report should look like below:

Title
INPUT_DATE - input date from front end

current week: # of events
previous week: # of events
current month: # of events
previous month: # of events
current year: # of events
previous year: # of events

I would want this done as a Define.

Directly in two lines (without DATEMOV):

-DEFAULT &XDATE=&YYMD.EVAL;

  
-SET &X1SUN=AYMD(&XDATE, -IMOD( DATECVT(&XDATE,'I8YYMD','W'), 7,'I1'), 'I8YYMD');
-SET &X2SAT=AYMD(&X1SUN,6,'I8YYMD');

-? &X

Explanation:

Get Focus day-of-week number (1=Mon, ..., 6=Sat, 7=Sun) from DATECVT;
Mod maps it to offset from Sunday (1,...,6,0),
subtract, with AYMD, to get BOW; add 6 to get EOW.

-SET &ECHO=ALL;
DEFINE FILE CAR
EXDATE/HYYMDS WITH COUNTRY = HINPUT(14, '20090317000000', 8, 'HYYMDS');
EXDATE1/YYMD = HDATE(EXDATE, 'YYMD');

-*-- DETERMINE MONDAY AND FRIDAY DATES ---
BOW1/YYMD = DATEMOV(EXDATE1, 'BOW');
EOW1/YYMD = DATEMOV(EXDATE1, 'EOW');

-*-- DETERMINE SUNDAY AND SATURDAY DATES ---
BOW2/YYMD = DATEADD(BOW1, 'DAY', -1);
EOW2/YYMD = DATEADD(EOW1, 'DAY', +1);

-*-- DETERMINE PREVIOUS SUNDAY AND SATURDAY DATES ---
BOWP/YYMD = DATEADD(BOW2, 'DAY', -7);
EOWP/YYMD = DATEADD(EOW2, 'DAY', -7);

END
-RUN

TABLE FILE CAR
PRINT
EXDATE
EXDATE1

BOW1 EOW1
BOW2 EOW2
BOWP EOWP
END

EXDATE is your data column.

Francis,

Thank you for all your help. You help me figure out the current week and previous week and I figured out with your help the current month, current year but how would you figure the previous month since there are different amount of days in each month?

I figure for previous year i can do something like below:
This seems to work.

-DEFAULT &INPUT_DATE = '20090318'

-SET &BOY1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOY')),'YYMD','I8YYMD');
-SET &EOY1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOY')),'YYMD','I8YYMD');

-SET &BOYP = AYMD(&BOY1,-365,'I8YYMD');
-SET &EOYP = AYMD(&EOY1,-366,'I8YYMD');

Now that you know how to use the functions, you would:

Determine the beginning of the Month,
Subtract one day,
Determine the beginning of the Month of this new Date,

etc.

Thanks Francis. I used the code below to determine the previous beginning of the month and end of the month. I really appreciate all your help.

-*-- DETERMINE BEGINNING AND END OF MONTH ---
-SET &BOM1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-SET &EOM1 = DATECVT((DATEMOV((DATECVT(&INPUT_DATE,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');

-*-- DETERMINE PREVIOUS BEGINNING MONTH AND END OF MONTH ---
-SET &BOM2 = AYMD(&BOM1,-1,'I8YYMD');
-SET &BOM3 = DATECVT((DATEMOV((DATECVT(&BOM2,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-SET &EOM3 = DATECVT((DATEMOV((DATECVT(&BOM2,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');