Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Calculate week number from date
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Calculate week number from date
 Login/Join
 
Member
posted
Hi all, I am calculating weeknumber from date calculated using AYMD function. when I try to run the below syntax,
&YEAR_AND_WEEK is displaying 2016-54 instead of 2017-01.


-DEFAULT &REPORT_DATE = '20170106';
-SET &REPORT_DATE = AYMD(&YYMD, -24, 'I8YYMD');
-*-SET &TODAY_DT= &YYMD;
-SET &YEAR = EDIT(&REPORT_DATE,'9999$$$$$$$$') ;
-SET &WEEK = HPART(HDTTM(DATECVT(&REPORT_DATE, I8YYMD, YYMD), 8, 'HYYMDs'), 'WEEK', 'I2');
-SET &WEEK_OF_YEAR=&WEEK+1;
-SET &YEAR_AND_WEEK=&YEAR|| '-' || &WEEK_OF_YEAR;
-SET &WEEKSTART_DT=DATECVT(DATEADD(DATEMOV(DATECVT(&REPORT_DATE, 'I8YYMD','YYMD'), 'BOW'), 'D', -1), 'YYMD', 'A8YYMD');
-SET &WEEKEND_DT=DATECVT(DATEADD(DATEMOV(DATECVT(&REPORT_DATE, 'I8YYMD','YYMD'), 'EOW'), 'D', 1), 'YYMD', 'A8YYMD');
-SET &START_DATE = EDIT(&WEEKSTART_DT,'$$$$$$99')|| '/' ||EDIT(&WEEKSTART_DT,'$$$$99$$$$')|| '/' ||EDIT(&WEEKSTART_DT,'9999$$$$$$$$');
-SET &END_DATE = EDIT(&WEEKEND_DT,'$$$$$$99')|| '/' ||EDIT(&WEEKEND_DT,'$$$$99$$$$')|| '/' ||EDIT(&WEEKEND_DT,'9999$$$$$$$$');
-TYPE &YEAR_AND_WEEK
-TYPE &START_DATE
-TYPE &END_DATE

Can anyone help please.

Thanks in advance.

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: December 05, 2016Reply With QuoteReport This Post
Master
posted Hide Post
...did you notice you're using &YYMD in the second line and not &REPORT_DATE ?

&YYMD = 20170124 today ( for me ).

-/- 24 = 20161231 which -is- week 53 ( depending on what system you use.. but your code gives 53 )

then for some reason you add 1


54 is the anwer.



Perhaps you should first add 7 days -before- the HPART 'week' instead of assuming +1 is always correct.


Good luck,
Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
make sure you also use the right SET WEEKFIRST


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 416 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Consider using a date dimension table with all of these values pre-calculated. That way you could just do an ON TABLE HOLD and -READFILE to parameterize all date values.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi RadhikaC,
Assumptions:
Week 1 starts with 1 January
Week 2 starts on the first Sunday after 1 January

Calculate the number of days since the beginning of the year and divide by 7 for the number of weeks.
Initial conditions:
If the year starts on a Sunday add 1.
If the year starts on a different day, call it D, then week 1 is until Saturday.
If the day of the date is before D add 2 else add 1.

The code is wrapped in a loop to get random dates.
  
-* File radhikac01.fex
-SET &ECHO=OFF;
-DEFAULT &REPORT_DATE = '20170331', &S='1'
-REPEAT #DDD FOR &I FROM 1 TO 10;
-SET &S=IF &S EQ '1' THEN '-1' ELSE '1';
-SET &RANDAYS=&S * 182 * RDUNIF('D5.4');
-SET &REPORT_DATE = AYMD(&REPORT_DATE, &RANDAYS, 'I8YYMD');
-SET &YEAR = EDIT(&REPORT_DATE,'9999$$$$$$$$');
-SET &YEAR1=&YEAR | '0101';
-SET &DOW=DOWK('&REPORT_DATE.EVAL', 'A4');
-SET &DOW1=DOWK('&YEAR1.EVAL', 'A4');
-*-TYPE &REPORT_DATE &YEAR1 &DOW1
-SET &W=DECODE &DOW(SUN 7 MON 6 TUE 5 WED 4 THU 3 FRI 2 SAT 1);
-SET &W1=DECODE &DOW1(SUN 7 MON 6 TUE 5 WED 4 THU 3 FRI 2 SAT 1);
-SET &DAYS=DATEDIF('&YEAR1.EVAL', '&REPORT_DATE.EVAL', 'D');
-*-TYPE &DAYS
-SET &INIT=IF &DOW1 EQ 'SUN' THEN 1 ELSE IF &DAYS LT &W1 THEN 1 ELSE 
-          IF &W GT &W1 THEN 2 ELSE 1;
-SET &WEEKS=INT(&DAYS / 7) + &INIT ;
-SET &YEAR_AND_WEEK=&YEAR|| '-' || &WEEKS; 
-TYPE &REPORT_DATE &YEAR_AND_WEEK
-#DDD


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
See here: http://infocenter.informationb.../source/topic166.htm

That gives you the correct year with the week number when January 1st is in week 53 or December 31st is in week 1 and other similar cases where week and year change over on different days.

Remember to set WEEKFIRST to the week numbering scheme that applies to your case, as is mentioned in that link as well.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1655 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
i had the similar issue. So i ended up using ON SAVE and -READ from it

-* To manually set your start week use the below line (1-7 Sun-Sat)
-*SET WEEKFIRST=1

DEFINE FILE CAR
TODAY/HYYMDs = HDTTM(DATECVT(&MDYY, 'I8MDYY', 'MDYY'), 8,'HYYMDs');
WEEK/I2 = HPART(TODAY,'WEEK','I2');
END

TABLE FILE CAR
PRINT
WEEK
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
ON TABLE SAVE AS WEEKNO
END
-RUN
-READ WEEKNO &WEEKNO.A2

-SET &WEEK = &WEEKNO;


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 100 | Registered: September 10, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
That's a misleading comment you put with the SET WEEKFIRST statement. It sets the day of the week (1 to 7) that the week starts with. You can set it to ISO too.

Additionally, you don't need the TABLE request in your case at all:
SET WEEKFIRST=ISO
-RUN
-SET &WEEK = HPART(HGETC(8, 'HYYMD'), 'WEEK', 'I2');


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1655 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Calculate week number from date

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.