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] Calculating the Week start date from Weekend date.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Calculating the Week start date from Weekend date.
 Login/Join
 
Arpita
Gold member
posted
Hi,
I am trying to calculate the Week start date from the Weekend date.
I used the date function BOW.
But i m not getting the correct result.

Please anyone can suggest.

Thanks for help in Advance.

Thanks
Arpita

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 43 | Registered: October 04, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
What do you get and what are you expecting? Please give us example dates.


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
FrankDutch
Virtuoso
posted Hide Post
Arpita

did you do a search on this site?
if you do you get many many results

please try if this will help you.

you yourself have asked this before and came whit the same remark

it has all to do with some basic settings




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
Arpita
Gold member
posted Hide Post
Hi Francis,
Thanks for your reply.
My weekend date is in YYMD format.
i have converted it into I8YYMD format.
Now the weekend date look like 2011/01/08
I have tried to calculate the Week start date using following syntax:
 

LASTDATE /I8YYMD = WEEK_END_DATE;
STDT_1/YYMD = DATECVT(( DATEMOV((DATECVT(LASTDATE,'I8YYMD','YYMD')),'BOW')),'YYMD','I8YYMD');

My start date coming as 2011/01/10.
It should come as 2011/01/02.

Can you please suggest.

Thanks
Arpita


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 43 | Registered: October 04, 2010Report This Post
Prarie
Virtuoso
posted Hide Post
So you want your week to start on Sunday? Then you have to tell it.
SET WEEKFIRST = 1
But as Frank suggested...do a search on the forum on this topic...there are tons of discussions...
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
Arpita

this is an interesting issue

when I run this the result is the same
appreantly according to this the week seens to START with the weekEND and when you go to BOW it uses the first WORKDAY of that week that seems to be a Monday.
If you do a EOW you will get the Friday being the last workday.

You expect to go to the Sunday, the last one before the day you entered.

In my opinion I would expect the weekend should be the END of the week...(as the name suggests)

I would do a work around in your case to get what you want.
It needs however some thinking. What would you expect when you work on Sunday, wil that be the actual BOW.
If so you need first to test whatday it is you are working on. If it is a sunday then that will also be the BOW day.
If it is a Saturday, the BOW should be the last Sunday before, so since he formule will move youe to the next monday you need to substract 8 days.

Will this help?




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
Francis Mariani
Expert
posted Hide Post
A few notes:

  • If your field is in YYMD format, you do not need to convert to I8YYMD and then use DATECVT to convert back to a Date field
  • Yes, it's weird behaviour, but something we have had to live with for a long time
  • I don't think WEEKFIRST makes any difference
  • I recently answered a question about this odd behaviour, but cannot find it using FocalPoint Search and took a while finding it using Google, but here it is: http://forums.informationbuild...227013106#1227013106

This is a simple fex that can be used to test dates
DEFINE FILE CAR
DATE_1/YYMDW = '20110109';
END

TABLE FILE CAR
PRINT
DATE_1
COMPUTE DATE_1W/W = DATE_1;
COMPUTE DATE_1N/YYMDW = 
IF DATE_1W IN (6,7) THEN DATE_1 - 2 ELSE DATE_1;

COMPUTE DATE_BOW/YYMDW = DATEMOV(DATE_1N, 'BOW');

COMPUTE DATE_EOW/YYMDW = DATEMOV(DATE_1N, 'EOW');

BY COUNTRY
WHERE RECORDLIMIT EQ 1
END

This example subtracts two days from the date when the date is a Saturday or Sunday, thus you get the correct BOW.
I have coded YYMDW for illustrative reasons only, to display the day-of-week - this can be substituted with YYMD.


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
Arpita
Gold member
posted Hide Post
Hi All,
Thanks a lot for your reply.
I have tried one syntax of my own.
And suprisingly it has worked.

Thanks you all for your replies.
Thanks all for your valuable time and suggestion.

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] Calculating the Week start date from Weekend date.

Copyright © 1996-2020 Information Builders