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>,

[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.

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.

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).

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.

Darin, SET WEEKFIRST = 2 fixed the problem.

Thanks!

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.

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

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.

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

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.

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,

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.

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

we are quoting ibi's date-time expert in saying that hpart is expected to be iso compliant.
The definition here is theirs.

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.

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.

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.

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?

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.
Kerry, reminding you please to follow up on this for us.

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

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

ah! the focwizard to the rescue. stay tuned, everybody.

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

I've tested the ISO2 today with server 7.6.5 and it works like a charm!

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.

Is the "DATEADD?" post related in any way?
Thanks, Doug

No, you just need more coffee

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