Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Find the start date from a week unit

 Go Search Notify Tools
 [SOLVED] Find the start date from a week unit
Gold member
 posted April 26, 2013 05:05 PM
I have the week numbers 1-52 in a table by year is there a way to figure out what the start date is for a week?
For example:
week 5 year 2013 equates to the start of week which is a monday would equal 2013/01/28.

or week 6 year 2013 equates to the start of week which is a monday would equal 2013/02/04.

Please let me know if anyone has a solution.

This message has been edited. Last edited by: <Kathryn Henning>,

8.0.9, Windows
excel / pdf

 Posts: 89 | Registered: October 21, 2010 IP
Virtuoso
 posted April 27, 2013 01:38 AM Hide Post
Calculating with week numbers should be something like
Begin_of_year + wn*7=daynr

Convert this to a webfocus formula

Search on calculating with smartdates

 Frank prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7test: WF 7.6.10 on the same platform and databases,IE7

 Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006 IP
Virtuoso
 posted April 27, 2013 05:31 AM Hide Post
I would tend to move functions. So for Dialogue Manager:
```SET WEEKFIRST=ISO2
-RUN
-* Supply week and year from the table
-SET &TABLE_YEAR     = 2013;
-SET &TABLE_WEEK     = 6;
-* Set the maximum week for the year to check for error.
-SET &WEEK_FOR_PREV  = HPART(HINPUT(8,EDIT(&TABLE_YEAR)|'1231',8,'HYYMDS'),'week','I2');
-* Does the first week of the year contain a Thursday?  If so subtract 1 from &TABLE_WEEK.
-SET &TABLE_WEEK     = IF HPART(HINPUT(8,EDIT(&TABLE_YEAR) |'0101',8,'HYYMDS'),'dw','I1') LE '5' THEN &TABLE_WEEK - 1 ELSE &TABLE_WEEK;
-* Get current date and get the current week and year, subtract from the supplied values.
-SET &WEEK_DIFF      = &TABLE_WEEK - HPART(HGETC(8,'HYYMDS'),'week','I2') ;
-SET &YEAR_DIFF      = &TABLE_YEAR - HPART(HGETC(8,'HYYMDS'),'year','I2') ;
-* Move current date to equivalent in supplied week
-SET &ACT_YEAR_DT    = HADD(HGETC(8,'HYYMDS'), 'year', &YEAR_DIFF, 8, 'HYYMDS');
-SET &ACT_WEEK_DT    = HADD(&ACT_YEAR_DT, 'week', &WEEK_DIFF, 8, 'HYYMDS');
-* Is equivalent a Sat or Sun?  If so subtract 1 week from &ACT_WEEK_DT, otherwise the following week will be retrieved with DATEMOV
-SET &ACT_WEEK_MV    = IF (HPART(&ACT_WEEK_DT,'dw','I1') EQ '7' OR HPART(&ACT_WEEK_DT,'dw','I1') EQ '1') THEN HADD(&ACT_WEEK_DT,'week',-1,8,'HYYMDS') ELSE &ACT_WEEK_DT;
-* Move equivalent week to begining of week, and convert to display value and check for an incorrect week.
-SET &CALC_DATE      = IF &TABLE_WEEK GT &WEEK_FOR_PREV THEN 'Error' ELSE HCNVRT(HDTTM(DATEMOV(HDATE(&ACT_WEEK_MV,'YYMD'),'BOW'),8,'HYYMDS'), '(HYYMD)', 10, 'A10');
-TYPE Beginning of week &CALC_DATE
```
or within a TABLE
```SET WEEKFIRST=ISO2
DEFINE FILE SYSCOLUM
TABLE_YEAR/I4         = 2013;
TABLE_WEEK/I2         = 6;
WEEK_FOR_PREV/I2      = HPART(HINPUT(8,EDIT(TABLE_YEAR)|'1231',8,'HYYMDS'),'week','I2');
TABLE_WEEK_MOV/I4     = IF HPART(HINPUT(8,EDIT(TABLE_YEAR)|'0101',8,'HYYMDS'),'dw','I1') LE 5 THEN TABLE_WEEK-1 ELSE TABLE_WEEK;
WEEK_DIFF/I4          = TABLE_WEEK_MOV - HPART(HGETC(8,'HYYMDS'),'week','I4') ;
YEAR_DIFF/I4          = TABLE_YEAR - HPART(HGETC(8,'HYYMDS'),'year','I4') ;
ACT_YEAR_DT/HYYMDS    = HADD(HGETC(8,'HYYMDS'), 'year', YEAR_DIFF, 8, 'HYYMDS');
ACT_WEEK_DT/HYYMDS    = HADD(ACT_YEAR_DT, 'week', WEEK_DIFF, 8, 'HYYMDS');
ACT_WEEK_MV/HYYMDS    = IF (HPART(ACT_WEEK_DT,'dw','I1') EQ 7 OR HPART(ACT_WEEK_DT,'dw','I1') EQ 1) THEN HADD(ACT_WEEK_DT,'week',-1,8,'HYYMDS') ELSE ACT_WEEK_DT;
CALC_DATE/A10         = IF TABLE_WEEK GT WEEK_FOR_PREV THEN 'Error' ELSE HCNVRT(HDTTM(DATEMOV(HDATE(ACT_WEEK_DT,'YYMD'),'BOW'),8,'HYYMDS'), '(HYYMD)', 10, 'A10');
END
TABLE FILE SYSCOLUM
PRINT TABLE_YEAR TABLE_WEEK CALC_DATE
BY TBNAME NOPRINT
END
```

[Changed logic as there was an error that would give an incorrect data for years following a 53 week year. Also ensure that these are ISO type week numbers, as that is the approach this follows.]

This message has been edited. Last edited by: Alan B,

Alan.
WF 7.705/8.007

 Posts: 1451 | Location: Portugal | Registered: February 07, 2007 IP
Silver Member
 posted April 27, 2013 10:32 AM Hide Post
Try this approach:
DEFINE FILE ...
DAYOFYEAR/I3 = WEEK * 7 ;
YEARSTART/MDYY = '01012013' ;
WEEKSTART/YYMD = YEARSTART + DAYOFYEAR -2 ;
END

DAYOFYEAR takes the week number time 7.
YEARSTART is a the literal start date of the year.
If you have multiple years in the file you will have to decide which year.
WEEKSTART computes a SMARTDATE by adding the DAYOFYEAR to YEARSTART; the -2 adjustment is because the year 2013 started on a Tuesday. The adjustment would only be 1 if you prefer a Monday date.
Again if you have multiple years in the file, you will need to work out the adjustment.
The DATETRAN function should help.
The DATEMOV function with the BOW option can also do this (BOW=begin of week).

Hope this helps.

WebFOCUS 8.0.2, FOCUS since 1977 - John@Aviter.com
PDF , Excel, FOCUS, Author of the Keysheets and Dates book.
www.Aviter.com

 Posts: 40 | Registered: April 19, 2013 IP
Virtuoso
 posted April 28, 2013 12:49 PM Hide Post
So previously I started from current date and went forward or backwards, this method starts at the beginning of the year selected and moves forward, or static if week one:
```SET WEEKFIRST=ISO2
-RUN
-* Supply week and year from the table
-SET &TABLE_YEAR     = 2028;
-SET &TABLE_WEEK     = 1;

-* If the 2nd January is not in week 1 of the selected year - could be week 52/53 of previous year, the 3rd will be,
-* and even if the 2nd is in week 1 and falls on a Saturday or Sunday, DATEMOV with BOW will move to the following Monday.
-* Main Calculation - INPUT the DATETIME of the 2nd or 3rd, ADD the required week, convert to DATE, Move to the Monday(BOW), Convert back to DATETIME and then to alpha YYYY/MM/DD.
-* Error if week is not valid.
-SET &CALC_DATE =
- IF &TABLE_WEEK LT 1 THEN 'Error' ELSE
- IF &TABLE_WEEK GT HPART(HINPUT(8,EDIT(&TABLE_YEAR)|'1231',8,'HYYMDS'),'week','I2') THEN 'Error' ELSE
- IF HYYWD(HINPUT(8,EDIT(&TABLE_YEAR)|'0102',8,'HYYMDS'),'A10') CONTAINS &TABLE_YEAR THEN
-TYPE &TABLE_YEAR is &CALC_DATE
```

or
```SET WEEKFIRST=ISO2
DEFINE FILE SYSCOLUM
TABLE_YEAR/I4         = 2021;
TABLE_WEEK/I2         = 1;
CALC_DATE/A10         = IF TABLE_WEEK LT 1 THEN 'Error' ELSE
IF TABLE_WEEK GT HPART(HINPUT(8,EDIT(TABLE_YEAR)|'1231',8,'HYYMDS'),'week','I2') THEN 'Error' ELSE
IF HYYWD(HINPUT(8,EDIT(TABLE_YEAR)|'0102',8,'HYYMDS'),'A10') CONTAINS EDIT(TABLE_YEAR) THEN
END
TABLE FILE SYSCOLUM
PRINT TABLE_YEAR TABLE_WEEK CALC_DATE
BY TBNAME
END
```

2021 and 2028 week one are years where the 2nd and 3rd are Sat/Sun or Sun/Mon as examples.

Alan.
WF 7.705/8.007

 Posts: 1451 | Location: Portugal | Registered: February 07, 2007 IP
Gold member
 posted April 29, 2013 09:00 AM Hide Post
I just tried Alan B's suggestions and it works great thank everyone for your suggestions.

8.0.9, Windows
excel / pdf

 Posts: 89 | Registered: October 21, 2010 IP