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.
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
January 25, 2017, 08:44 AM
Danny-SRL
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.
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 :
January 26, 2017, 10:13 AM
WF1326
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
January 26, 2017, 10:30 AM
Wep5622
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 :