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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name. Connect to myibi
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculate current week

 Go Search Notify Tools
 [SOLVED] Calculate current week
Guru
 posted November 19, 2007 09:46 AM
Hi all,

I'm trying to calculate the current week. I've got the next solution:

DEFINE FILE CAR
DATE1/A10 = TODAY(DATE);
DATE2/HMDYYs = HINPUT(14, DATE1, 8, 'HMDYYs');
WEEK/I2 = HPART(&DATE2, 'WEEK', 'I2');
END

TABLE FILE CAR
SUM
MAX.DATE1
MAX.DATE2
MAX.WEEK
END

When I do this I get the next result:

11/19/2007 | 11/19/2007 00:00:00.000 | 53

So WF takes the maximum possible week, while the other field represent today. What is going wrong?

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

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 454 | Location: Europe | Registered: February 05, 2007 IP
Virtuoso
 posted November 19, 2007 09:59 AM Hide Post
[quote]When I do this I get the next result:

11/19/2007 | 11/19/2007 00:00:00.000 | 53[/quote}

When you say what is going wrong? 11/19/2007 is todays date, I believe you are doing the calculation in the TODAY function, is 'DATE' a field in your data base? So your max.date1 is today. I've not worked with date time formats much about all I can do is ask you questions here.

Leah

 Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004 IP
Virtuoso
 posted November 19, 2007 10:05 AM Hide Post
I just changed two little thing, and for me it runs fine now.
My code:
```DEFINE FILE CAR
DATE1/A10 WITH COUNTRY= TODAY(DATE);
DATE2/HMDYYs = HINPUT(14, DATE1, 8, 'HMDYYs');
WEEK/I2 = HPART(DATE2, 'WEEK', 'I2');
END

TABLE FILE CAR
SUM
MAX.DATE1
MAX.DATE2
MAX.WEEK
END
```

I added WITH COUNTRY for the first field, and removed the & from DATE2.

GamP

 - Using AS 8.2.01 on Windows 10 - IE11. in Focus since 1988

 Posts: 1961 | Location: Netherlands | Registered: September 25, 2007 IP
Guru
 posted November 19, 2007 10:42 AM Hide Post
Hi Leah,

11/19/2007 is indeed the current date, I want to retrieve the current week from the current date.

Gamp, I've tried this one, now I get week 46 (the most recent record in the database is of week 46).

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 454 | Location: Europe | Registered: February 05, 2007 IP
Virtuoso
 posted November 19, 2007 01:24 PM Hide Post
Looks to me like your original code is working (except for using DATE2 instead of &DATE2. Today's date actually DOES fall in week 46 (or week 47) depending on what you count as the first day of the week. (See the SET WEEKFIRST paramater) If you hard code the date in your code to 20071124, you get week 47. If you add SET WEEKFIRST = 2 (meaning Monday) your code results in week 47.

Regards,

Darin

In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat

 Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007 IP
Guru
 posted November 20, 2007 06:01 AM Hide Post
Darin, SET WEEKFIRST = 2 fixed the problem.

Thanks!

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 454 | Location: Europe | Registered: February 05, 2007 IP
Expert
 posted November 23, 2007 05:30 AM Hide Post
something to keep in mind is that weeknumber has an ISO
standard and tinkering with WEEKFIRST violates that standard, which may be pefectly ok w/u.
-S.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Member
 posted November 25, 2007 05:41 PM Hide Post
Frans,

You might want to concider putting the SET WEEKFIRST = n command in your EDASPROF.PRF (alongside SET CDN = ON). Then everyones week will be the same, (and you wont need to set it for every report that uses weeks).

Sucess

Paul Burridge
Senior Consultant
34 years with Information Builders
WebFOCUS 8.2.5 Win10

 Posts: 17 | Location: The Netherlands | Registered: February 21, 2005 IP
Virtuoso
 posted January 21, 2008 07:46 AM Hide Post
quote:
Originally posted by susannah:
something to keep in mind is that weeknumber has an ISO
standard and tinkering with WEEKFIRST violates that standard, which may be pefectly ok w/u.
-S.

I was looking for the weeknumber and found this.

the interesting thing her is the reference to the ISO site. If you follow these rules. we would now (2008/01/21) be in week 4, but if you use the Webfocus formula

```WEEK/I2 = HPART('DATE', 'WEEK', 'I2');
```

You get week 3.

 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
Expert
 posted January 21, 2008 08:56 AM Hide Post
wow. Frank you're so right. Renee, the date-time guru, needs to comment on this one.
ISO is definitely week 4, and focus is definitely saying week3.
Did you notice also that DATE2 is only getting calculated for the 1st record in the dataset, so WEEK doesn't get calc'd at all for the rest of the records.
And if you try to cure that, DATE2 and WEEK don't get calculated at all.
hmmm
```DEFINE FILE CAR
DATE1/A10 WITH COUNTRY= '01/21/2008' ;
DATE2/HMDYYs = HINPUT(14, DATE1, 8, 'HMDYYs');
WEEK/I2 = HPART(DATE2, 'WEEK', 'I2');
END
-RUN
TABLE FILE CAR
PRINT COUNTRY DATE1
ON TABLE HOLD
END
DEFINE FILE HOLD
DATE2/HMDYYs = HINPUT(14, DATE1, 8, 'HMDYYs');
WEEK/I2 = HPART(DATE2, 'WEEK', 'I2');
END
TABLE FILE HOLD PRINT COUNTRY DATE1 DATE2 WEEK
END
```

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Virtuoso
 posted January 21, 2008 09:37 AM Hide Post
Susannah

According to the ISO standard, the week number 1 is the week that holds the first Thursday of the year.
We can correct the calclution of the week by just adding 1 to the formula, but in that case the first and the last week of the year are still wrong.
So adjust it by finding the firstday of the year, (I mean DAY not DATE) if that is, like this year, a Tuesday you know the first Thursday is the 3th and that should be week 1.
IBI says WEEK 52.
I was looking in Microsoft, but that is also not every year ok.

I wonder if someone can create an always correct calcultion.

 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
Expert
 posted January 21, 2008 09:43 AM Hide Post
here's a "Known Problem"
apparently HPART doesn't work and its a known problem. I'ld say it doesn't seem to work on several levels..
also,
According to my recollection of what Renee had said, the first week of the year has 4 or more days in it, and I recall that she was referring to iso at that time. so the first saturday would have to be jan 4 or more.
Microsoft has it right for thisyear... today is week 4 using their WEEKNUM function
Boy, this is a mess.

Kerry, can you get Renee to chime in here for us?

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

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Gold member
 posted January 24, 2008 11:37 AM Hide Post
Frans,

Is the week you are after ISO week? When you say you want week, can you explain how your management defines week?

ISO week is computationally complex and I've never seen a site that uses this definition. Almost invariably, sites that use week use the concept of an absolute week that is specified by a week ending date.

Understand that when you use ISO week, that Jan 1 may fall in the previous year, and Dec 31 may fall in the following year. Thus, returning ISO week number means that HPART will certainly return the wrong year- e.g. ISO week 52 may include Jan 1 (of the next year). The year returned for HPART of Jan 1 will certainly be the calender year of Jan 1, but the ISO year will be the previous year!

If what you really want is an absolute week, with a week ending date, there are easy ways to provide this without using HPART.

All that said, if you think HPART is not correct, by all means open a call with IBI support services.

IBI Development

 Posts: 61 | Registered: November 15, 2005 IP
Guru
 posted January 24, 2008 11:56 AM Hide Post
Hi Edward,

No I was not really looking for ISO date's, absolute week will work fine, the solution to set weekfirst = 2 worked out fine for me at that time.

But you've made me curious, what is the easy way to get the current absolute week?

Frans

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 454 | Location: Europe | Registered: February 05, 2007 IP
Expert
 posted January 24, 2008 12:04 PM Hide Post
we are quoting ibi's date-time expert in saying that hpart is expected to be iso compliant.
The definition here is theirs.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Virtuoso
 posted January 24, 2008 01:50 PM Hide Post
Edward, Frans, Susannah

I worked this out in excel now, it is always correct there, but now I have to find a way to convert it to Webfocus.

 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
Expert
 posted January 24, 2008 01:59 PM Hide Post
which is why we keep asking Kerry to get Renee to visit these weeknumber threads and help. HPART isn't working the way its touted to work.

I've got a whole excel app for a client working with week number and it works like a charm, as you say.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Gold member
 posted January 24, 2008 03:13 PM Hide Post
Frans,

Ok, to begin, it's important to understand the concept of an absolute week: there is an infinite timeline of numbered weeks going into the past and the future. Each week number is unique, and each starts with a given day of the week. What day the week starts on, and the actual location of week 0, *are completely arbitrary* and depend on your site. To get the end-of-week from any input new date, use the following:

```MYTMP/YYMD  = MYNEW + 2 ;
MYDOW/I2  = MYTMP - (INT(MYTMP/7) * 7) ;
EOWDIF/I2 = IF MYDOW EQ 0 THEN 0 ELSE 7 - MYDOW ;
EOW/YYMD = MYNEW + EOWDIF ;

```

The definition of mytmp allows any arbitrary start of week. For the addition of 2, we get an ending week of Saturday, as most American calenders display. Change the 2 to different integers to get different end (start) days of the week.

IBI Development

 Posts: 61 | Registered: November 15, 2005 IP
Gold member
 posted January 31, 2008 02:43 PM Hide Post
Following up on this thread, has anyone established a method of computing the ISO week number for a given date that will work for any given date of any given year?

------------------------------------------
DevStudio 8.2.03
WFS 8.2.03

 Posts: 86 | Location: Atlanta | Registered: May 10, 2007 IP
Expert
 posted January 31, 2008 05:04 PM Hide Post
Thru our moderator Kathleen Butler, we got in touch with Renee who is the ibi guru on the HPART function, and made her aware of this thread. She has promised to take a look at it and post back.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Platinum Member
 posted January 31, 2008 05:21 PM Hide Post
Hi

Funny that this is coming up now. We always show weeknum in our reports and if you use the webfocus funtions you will end up with week0 and week53. I was just asked to fix this last week. We wanted to define the first day of the first week. So for example we wanted Monday 20071231 to be day one of week 1 of 2008. So I wrote the following function. This is not a great solution of course since you need to manually configure each year but it meets our requirements.

SET WEEKFIRST = 2
DEFINE FUNCTION GET_WKNM(THEDATE/YYMD)
MY_DATE_TIME/HYYMDS = HDTTM(THEDATE, 8, 'HYYMDS');
MYWEEKNUMTEMP/A10 = HNAME(MY_DATE_TIME ,'WEEK','A10');
X_WKNUM/I2=
IF THEDATE GE 20071231 AND THEDATE LE 20080106 THEN 1
ELSE IF THEDATE GE 20080107 AND THEDATE LE 20081228 THEN EDIT(MYWEEKNUMTEMP) + 1
ELSE IF THEDATE GE 20070101 AND THEDATE LE 20071230 THEN EDIT(MYWEEKNUMTEMP)
ELSE IF THEDATE EQ 20060101 THEN 52
ELSE IF THEDATE GE 20060102 AND THEDATE LE 20061231 THEN EDIT(MYWEEKNUMTEMP)
ELSE IF THEDATE GE 20050101 AND THEDATE LE 20050102 THEN 52
ELSE IF THEDATE GE 20050103 AND THEDATE LE 20051231 THEN EDIT(MYWEEKNUMTEMP)
ELSE IF THEDATE GE 20081229 AND THEDATE LE 20090104 THEN 1
ELSE IF THEDATE GE 20090105 AND THEDATE LE 20091231 THEN EDIT(MYWEEKNUMTEMP) + 1
ELSE 99;
GET_WKNM/A2=EDIT(X_WKNUM);
END

WF 8.0.0.5M

 Posts: 246 | Location: Montreal, QC, Canada | Registered: October 01, 2003 IP
Member
 posted January 31, 2008 07:14 PM Hide Post
Hi Susannah:

I have reviewed the thread and there does appear to be an issue with HPART and HNAME that utilize the WEEK component. I know there was an earlier issue reported that was addressed. There does appear to be a problem and I am currently discussing this issue with Product Management. Susannah, I will open a separate case for you to track this issue but I will also update this thread when I have more details on the matter. I should have more details on Monday.

Renee

 Posts: 3 | Registered: August 25, 2004 IP
Expert
 posted January 31, 2008 08:24 PM Hide Post
ah! the focwizard to the rescue. stay tuned, everybody.

 In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

 Posts: 3811 | Location: Manhattan | Registered: October 28, 2003 IP
Member
 posted February 04, 2008 07:25 PM Hide Post
Dear Susannah:

Calculating the week number can result in different values, depending on how you
define a week. I believe Ed discussed this earlier in the thread in detail.
At this time, we count week 1 as the first full week in the year, starting at
with the day of WEEKFIRST (SET WEEKFIRST defaults to 7). This is not how ISO
does it. They do it as the first week containing 4 or more days.

At this time, Product Management is looking to enhance the WEEKFIRST setting
to handle 7 additional options, for ISO1 thru ISO7 for an ISO compliant
week while retaining the current behavior in order to avoid upward
compatibility issues for sites depending the current behavior.

Note that the ISO 8601 standard also establishes Monday as the first day of the
week, so for complete ISO compliance the setting for WEEKFIRST should be ISO2.
However, we will allow all the other values, and do our calculations of week
numbers accordingly (HPART and HNAME subroutines using the WEEK component).

Regards,
Renee

 Posts: 3 | Registered: August 25, 2004 IP
Guru
 posted June 11, 2008 08:55 AM Hide Post
I've tested the ISO2 today with server 7.6.5 and it works like a charm!

Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.

 Posts: 454 | Location: Europe | Registered: February 05, 2007 IP
Virtuoso
 posted December 01, 2014 08:44 AM Hide Post
Just this weekend this topic came up on the PostgreSQL mailing list (which has some tremendously smart people) and I found their case illustrative of the problem. Here's a link to the archived thread.

It boils down to the following; if you use ISO week numbers, you also need to use the ISO year to get the correct year for weeks where the normal year changes.

For example, if you would use the calendar year number with the ISO week, you incorrectly get "Week 1, 2014" for December 31st of 2014 and (correctly) "Week 1, 2015" for January 1st 2015.

OTOH, If you use the ISO year, which starts at week 1, you correctly get "Week 1, 2015" for both cases.

That's why ANSI/ISO SQL provides to_char formatters for both 'IYYY' and 'IWW', they are related!
Unfortunately, it doesn't seem that HPART provides a component to retrieve the ISO year, only the calendar year.

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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010 IP
Expert
 posted December 01, 2014 09:45 AM Hide Post
Is the "DATEADD?" post related in any way?
Thanks, Doug

 Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005 IP
Virtuoso
 posted December 01, 2014 09:48 AM Hide Post
No, you just need more coffee

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: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010 IP
Member
 posted July 15, 2015 07:59 PM Hide Post
TABLE FILE CAR
PRINT
CAR.COMP.CAR
COMPUTE TODAY11/HYYMDS = HGETC(8, TODAY);
COMPUTE NEWDATE22/YYMD = HDATE(TODAY11, 'YYMD');
COMPUTE NEWDATE33/I8YYMD = NEWDATE22;
COMPUTE DAY_WEEK/A4 = DOWK(NEWDATE33,'A4');
BY COUNTRY
END

 Posts: 2 | Registered: March 02, 2011 IP