Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Related to previous post(Getting the last 11 week data from current week)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Related to previous post(Getting the last 11 week data from current week)
 Login/Join
 
Gold member
posted
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,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 43 | Registered: October 04, 2010Report This Post
Virtuoso
posted Hide Post
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');


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
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
 
Posts: 43 | Registered: October 04, 2010Report This Post
Virtuoso
posted Hide Post
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.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
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




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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
-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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
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
 
Posts: 43 | Registered: October 04, 2010Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 43 | Registered: October 04, 2010Report This Post
Expert
posted Hide Post
-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.


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 43 | Registered: October 04, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Related to previous post(Getting the last 11 week data from current week)

Copyright © 1996-2020 Information Builders