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
WebFOCUS 7.6 Windows, All Outputs
March 22, 2011, 09:33 AM
GamP
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
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 28, 2011, 06:25 AM
GamP
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 ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
March 28, 2011, 09:35 AM
Francis Mariani
-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');
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 28, 2011, 09:48 AM
FrankDutch
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...
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 28, 2011, 01:08 PM
Arpita
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
WebFOCUS 7.6 Windows, All Outputs
March 28, 2011, 01:09 PM
Francis Mariani
Did you see my reply?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 28, 2011, 01:14 PM
Arpita
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.
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 29, 2011, 05:26 AM
FrankDutch
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.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 29, 2011, 05:27 AM
FrankDutch
And post the whole code you are using now that gives you the error.
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
March 29, 2011, 05:32 AM
GamP
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 ...
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
March 29, 2011, 09:24 AM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 01, 2011, 03:41 AM
Arpita
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.