Hi Frank,
My query is related to my previous post, in which we have to calculate the last 11 Week date.
Thanks in advance.

Arpita

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

You can try two things:

-SET &STARTDATE =DATECVT((DATEADD((DATECVT(&LASTDATE,'I8DMYY','DMYY')),'D', -84)),'YYMD','I8YYMD');

If that does not work then use the edit method to change the date foramt of the &var:

-SET &LDATE = EDIT(&LASTDATE,'$$$$9999') | EDIT(&LASTDATE,'$$99') | EDIT(&LASTDATE,'99');
-SET &STARTDATE =DATECVT((DATEADD((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');

Hi Gamp,
Thanks a lot for your reply.
Sorry to say its not working.

Actually,my problem is,
I am getting one date called to_date(format-DMYY). this date is user input.
Based on this date only, we have to calculate, the last 11 week date.

The syntax which frank suggested is working fine.
-SET &STARTDATE =DATECVT((DATEADD((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');


The only problem i am facing is to calculate the (&LASTDATE), as the &LASTDATE is coming from &TO_DATE(DMYY).
-DEFAULT &TO_DATE = 'FOC_NONE';

Please suggest.

Thanks in advance.

Arpita

Arpita,
The code that you already have is almost correct, as far as I can see. You already convert the input date from DMYY to YYMD and also compute the startdate correctly. Only thing is that you computed the &LASTDATE variable to be the literal string &LAST_DATE in stead of the value of &LAST_DATE and that you did not account for a default when FOC_NONE is active. The following code remedies both issues:

-* Front end date
-DEFAULT &TO_DATE = 'FOC_NONE'

-SET &LASTDATE  = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(&TO_DATE ,'I8DMYY','I8YYMD');
-SET &STARTDATE = DATECVT((DATEADD((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');

-TYPE Entered date: &TO_DATE Start date: &STARTDATE End date: &LASTDATE

Run this and see if it is what you're needing.

quote:

-SET &STARTDATE = DATECVT((DATEADD((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');

this line should be changed

to


-SET &STARTDATE = DATECVT((DATEADD((DATECVT('&LASTDATE.EVAL','I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');


the EVAL will help I suppose

Now that you showed what the output of the code is I could pinpoint the problem.
It now appears that your input date is separated by forward slashes. We have to account for those when doing the conversion. So the code that works for date values with slashes is:

-DEFAULT &TO_DATE = 'FOC_NONE';
-SET &LASTDATE = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(EDIT(&TO_DATE,'99$99$9999'),'A8DMYY','A8YYMD');
-SET &STARTDATE = DATECVT((DATEADD((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'D', -84)),'YYMD','I8YYMD');
-TYPE Entered date: &TO_DATE Start date: &STARTDATE End date: &LASTDATE

The things that need changing is that the slashes will have to be removed from the date (this is what EDIT does) and the result of this is Alphanumeric, so the format should be A8DMYY, not I8DMYY. Hope this helps ...

-SET &LASTDATE = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(EDIT(&TO_DATE,'99$99$9999'),'A8DMYY','A8YYMD'); AND COMPUTE EOW/A8YYMD = DATEMOV(&LASTDATE, 'EOW');

This not valid WebFOCUS syntax - COMPUTE is not Dialogue Manager syntax.

Try this:

-SET &LASTDATE = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(EDIT(&TO_DATE,'99$99$9999'),'A8DMYY','A8YYMD');
-SET &DATE_EOW = DATECVT((DATEMOV((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

Arpita

The end of week issue was also answerd in your first question.
I am sure we all are willing to help people, but it would be better if people read the given answers better and try to UNDERSTAND what we advice instead of just copying the given answers.
It is better to teach one how to catch a fish then just give it to him...

Hi Frank,
Sorry for my queries.
I agree in my 1st post i have mentioned we are considering the input date as weekend date.
But, now the requirement has changed.The input date can be any date. From that date we have to calculate the week end date and do the rest of the calculation.
Again sorry.

Thanks
Arpita

Did you see my reply?

Hi Francis,
Thanks a lot for your reply.
Sorry to say, it is not working.

I tried the suggested piece of code.
But it is not calculting the &DATE_EOW correctly.
Due to that the &STARTDATE calculation is also not coming properly.

Please advice.
Thanks in advance.

Thanks
Arpita

-SET &TO_DATE = '27/03/2011';

-SET &LASTDATE = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(EDIT(&TO_DATE,'99$99$9999'),'A8DMYY','A8YYMD');
-SET &DATE_EOW = DATECVT((DATEMOV((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-TYPE &TO_DATE - &LASTDATE - &DATE_EOW

Please try this code.

And this:

-SET &TO_DATE = 'FOC_NONE';

-SET &LASTDATE = IF &TO_DATE EQ 'FOC_NONE' THEN &YYMD ELSE DATECVT(EDIT(&TO_DATE,'99$99$9999'),'A8DMYY','A8YYMD');
-SET &DATE_EOW = DATECVT((DATEMOV((DATECVT(&LASTDATE,'I8YYMD','YYMD')),'EOW')),'YYMD','I8YYMD');

-TYPE &TO_DATE - &LASTDATE - &DATE_EOW

Arpita

When you get EOW=friday and you want it to be Saturday it has something to do with your country settings.
In my opinion end of week should be Sunday.

If you can not find out where that should be set, the most easy work arround could be to add 1 day at the result Francis has given you.

I think a good book for you will be "almost 1001 ways to work with dates in webfocus"
it will help you understand (almost) everything on dates and how to calculate, convert and work with them.
Please ask your boss to buy it for you.

And post the whole code you are using now that gives you the error.

Ok, let's try a different approach here. Take a look at the next code and see if this is what you need:

-DEFAULT &TO_DATE = 'FOC_NONE'
-SET &TO_DATE = IF &TO_DATE EQ 'FOC_NONE' THEN &DATEDMYY ELSE &TO_DATE;

TABLE FILE SYSCOLUM
SUM   COMPUTE SDAT/DMYY      = '&TO_DATE'; NOPRINT
      COMPUTE WD/W           = SDAT; NOPRINT
      COMPUTE TO_DATE/YYMD   = IF WD EQ 7 THEN SDAT + 6 ELSE SDAT + 6 - WD;
      COMPUTE STARTDATE/YYMD = TO_DATE - 84;
ON    TABLE SET PREVIEW ON
ON    TABLE SET HOLDLIST PRINTONLY
ON    TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD &LASTDATE.8. &STARTDATE.8.

-TYPE Entered date: &TO_DATE Start date: &STARTDATE End date: &LASTDATE

This way the calculation of the needed values is a little bit easier to code and understand.
Hope this helps ...

quote:

Actuaaly , it is calculating the EOW as friday's date, but the end of week is Saturday's date.

If you know EOW always gives you a Friday date and you always want a Saturday date, simple add 1 to the date provided by the EOW function - use the DATEADD function.

quote:

And also the &Startdate is not coming properly. From the front end i gave to_date as '23/03/2011'

That was taken care of in my example.

Hi GamP,
Thanks a lot for your reply.
Your syntax works fine for me.
Thanks all who have replied to my queries.
Thanks all, for your effort.

Thanks
Arpita