Alpha Video Training
Results 1 to 16 of 16

Thread: WEEK NUMBERS

  1. #1
    Ian Stewart
    Guest

    Default WEEK NUMBERS

    Anybody out there ever had the need to convert a date into a WEEK NUMBER. Not sure whether this is a UK format. For those not familiar with this term, week numbers run from 1 to 52 starting 1st January each year. Pretty obvious.
    I need to check if two order dates occur in the same week number and in the same year. Technically the change over from week 52 in one year to week 1 in the following year would not matter.
    Regards,
    Ian Stewart

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: WEEK NUMBERS

    You'll need to setup/build a table to contain date ranges for weeks and corresponding week numbers for a number of years into the future (decide for yourself how far into the future your application will be used).



    "week numbers run from 1 to 52 starting 1st January each year" - fine in concept but in real usage there have to be 53 weeks periodically/eventually (usually five or six or seven years) to keep the sequence in some sort of correlation to the calendar.

    Once you have the table built indicating where the fifty-third week falls, you do a lookup to that table based on the calendar date to return a week number.

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: WEEK NUMBERS

    Upon reconsideration of your post, my answer is incorrect, but still pertinent.

    What do you do with the 365th day of the year? Is that week 53 in and of itself?

    The procedure would seem to be to obtain the "day of the year", a number, then divide by 7 to obtain the "raw" week number (with decimal), then get the floor() of that number.

    I'd be glad to pursue the computation of the parts involved if this is not sufficient to your needs.

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: WEEK NUMBERS

    FLOOR((JDATE(DATE())-JDATE(CTOD("01/01/"+RIGHT(DTOC(DATE()),4))))/7)+1

    = 5.000000



    I've always worked with Alpha Five in some version or another. I'm assuming that floor(), jdate(), and ctod() are valid in Alpha Four.

    If you are passing a variable or field value to the expression...
    g
    FLOOR((JDATE(datevalue)-JDATE(CTOD("01/01/"+RIGHT(DTOC(datevalue),4))))/7)+1

  5. #5
    Ian Stewart
    Guest

    Default RE: WEEK NUMBERS

    Stan,
    Many thanks for your efforts. Unfortunately jdate is not available in Alpha4. I am however in the process of converting my applications to Alpha5 and with the jdate function the problem is easily solved. Again many thanks
    IAN

  6. #6
    Gedi Margaitis
    Guest

    Default RE: WEEK NUMBERS

    If this requirement is for the current year only, then a solution would be to use a CASE() funtion or a script with the CASE{} command.

    You could compare the record's date against a hard-coded list of week ending dates to give you the week number.

    This is by no means an elegent solution, but it would work.

    gm

  7. #7
    Ian Stewart
    Guest

    Default RE: WEEK NUMBERS

    Thanks Gedi,
    Unfortunately there is not a case() function in Alpha4v6 although there is a case() statement in the script language.
    This whole query started off hoping their was a simple solution. It's not surprising as I did a internet search on "week numbers" and nobody seems to agree on what they are. They can't even agree what day a week starts with.
    I think Stan's solution using the jdate function in Alpha 5 will probably be the best.
    Many thanks,
    IAN

  8. #8
    Member
    Real Name
    Duncan Greenshields
    Join Date
    May 2000
    Location
    Toronto, ON
    Posts
    216

    Default RE: WEEK NUMBERS

    Ian;
    Actually I believe A4V6 is the first version of A4 with the "Case" function. By the way, is "jdate" "Julian Date"? If so, I believe it would not be difficult to construct a look-up table that would take care of your needs.

    Duncan

  9. #9
    Ian Stewart
    Guest

    Default RE: WEEK NUMBERS

    Thanks Duncan,
    Your right, I do have the case statement available (problem with using an old manual). Jdate apparently is a Julian date function in Alpha5 which would solve the problem (see Stan Mathews response above).
    Thanks
    Ian

  10. #10
    Ian Stewart
    Guest

    Default RE: WEEK NUMBERS

    Stan,
    Just to let you know I have solved my problem. I was beginning to lose sight of what I wanted to acheive, ie. checking if two dates occurred in the same week number. Since it doesn't matter what the actual week number is, 5,50,100 or whatever, I generated my own week numbers from an arbitary date (Sun 6 Jan 2002) and calculated a week number for each of the two dates to be compared.
    I had forgotten that you can actually subtract two dates in Alpha4 which generates a number. You can't do any arithmetic calculations on this number until it is enclosed in ().
    The code I used to generate a week number for each of the two dates is:
    FLOOR((DATE-CTOD("6-1-2002"))/7)+1

    Again, thanks for your help, it was the jdate idea that set me on the right track.

    IAN

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: WEEK NUMBERS

    Glad to hear it. I was just experimenting and, of course, you can subtract dates in Alpha Five as well. I had tried that early in my attempt to suggest a solution and must have had a typo in my efforts because it didn't work correctly. That's when I went to jdate().

  12. #12
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default RE: WEEK NUMBERS

    This is a script that will calculate the week number. There are two versions. Script 1 needs all three lines. Script 2 is all one line. These scripts use A4 V3 functions. They assume that a week is Sunday through Saturday. There could be 53 weeks in a year.

    Enter_Date is a date field in my table.
    I hope this helps.

    Stans script fails to take into account that the first day of the year is not always Sunday.



    'Script 1****************************************

    vndow = (dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(Enter_Date)
    vnumber = if(vndow " 1.00,1.00,vndow)
    ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + vnumber ) / 7)

    'Script 2****************************************

    ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + (if((dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(enter_date)"1.00,1.00,(dow(ctod("01-01-" + ltrim(str(year(Enter_Date))))) + 1) - dow(enter_date))) ) / 7)

  13. #13
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: WEEK NUMBERS

    "week numbers run from 1 to 52 starting 1st January each year"

    So "the first day of the year is not always Sunday" but it is always January 1, as the post stated.

  14. #14
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default RE: WEEK NUMBERS

    Stan my apologies I misunderstood if a week starts Jan 01 and ends Jan 07 than this will work for V4 with enter_date a field in the table.

    ceiling((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + 1 ) / 7)

  15. #15
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default RE: WEEK NUMBERS

    This might be better

    floor((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) ) / 7) + 1

  16. #16
    Member
    Real Name
    Allen Klimeck
    Join Date
    Apr 2000
    Location
    Colorado
    Posts
    539

    Default RE: WEEK NUMBERS

    One more time

    floor((((Enter_Date) - (ctod("01-01-" + ltrim(str(year(Enter_Date)))))) + 1 ) / 7)

Similar Threads

  1. week
    By Ton Spies in forum Archived Wishlist
    Replies: 5
    Last Post: 12-27-2004, 01:04 PM
  2. week no.?
    By nehru in forum Alpha Five Version 5
    Replies: 4
    Last Post: 05-23-2004, 08:03 AM
  3. Week Number
    By David Priest in forum Alpha Five Version 5
    Replies: 3
    Last Post: 08-12-2003, 07:24 AM
  4. Missing Numbers and Numbers Out of Order
    By Cheryl Franklin in forum Alpha Five Version 4
    Replies: 2
    Last Post: 12-20-2000, 11:34 AM
  5. 5th week
    By nick marodis in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-25-2000, 07:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •