Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or Mond...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jan 8, 2018 7:53 AM
(7099 views)

My company uses a fiscal calendar where the weeks start on Saturdays. According the the JSL WEEKOFYEAR() function we have two options: Rule 1 and 2 for weeks that start on Sundays, and Rule 3, for weeks that start on Mondays.

Has anyone created a JMP script to calculate the week of the year for weeks that start on a day other than Sunday or Monday?

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If you add a day to the calculation you will get what you want

```
Names Default To Here( 1 );
Week Of Year( Today() + In Days( 1 ) );
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sorry the delay in replying. Your solution works well. Thank you.

Just to make sure that the solution is clear,

@txnelson wrote:Good catch, here is a simple solution

`Names Default To Here( 1 ); testdate = Date MDY( 12, 31, 2017 ); Week Of Year( testdate + If( Year( testdate ) == Year( testdate + In Days( 1 ) ), In Days( 1 ), 0 ) );`

by adding 1 day to testdate with the In Days(1) function, Sunday = 2, Monday = 3, ... Friday = 7 and Saturday wraps around to be 1 again since there is no 8th day in a week.

I also like that you can define a variable within a formula. Very helpful!

5 REPLIES 5

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

If you add a day to the calculation you will get what you want

```
Names Default To Here( 1 );
Week Of Year( Today() + In Days( 1 ) );
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or Monday

Thank you txnelson!

I like your answer better than the one I came up with. Your answer is more efficient...

I have a table of dates, and used three separate formulas to add a day to the fiscal Week only if the day is Saturday:

In one column, Fiscal_Week, I use the WeekofYear() function: Week Of Year(:TIMESTAMP)

In the next column, DayOfWeek, I determine which day of the week it is: Day Of Week(:ET_TIME)

And in the third column, CompanyFiscalWeek I was able to get the correct value using the formula:

If (:Day of Week == 7, :Fiscal_Week + 1, :Fiscal_Week )

I'll use your solution instead. Thanks !

Jens

I like your answer better than the one I came up with. Your answer is more efficient...

I have a table of dates, and used three separate formulas to add a day to the fiscal Week only if the day is Saturday:

In one column, Fiscal_Week, I use the WeekofYear() function: Week Of Year(:TIMESTAMP)

In the next column, DayOfWeek, I determine which day of the week it is: Day Of Week(:ET_TIME)

And in the third column, CompanyFiscalWeek I was able to get the correct value using the formula:

If (:Day of Week == 7, :Fiscal_Week + 1, :Fiscal_Week )

I'll use your solution instead. Thanks !

Jens

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or Monday

I just noticed that the calculation appears to break down at the end of the year.

In the example for this year, 9/30/17 is the beginning of week 41. All other weeks starting on Saturday properly increment up through 12/30/17 wich is the begining of week 54.

For some reason, rather than continuing with week 54 for the next 6 days, or skipping week 54 and starting week 1, JMP labels Saturday 12/30 as week 54, and Sunday 12/31 as the first day of Week 1 in 2018. Looks like a flaw, since weeks should not start on two different days over a data set?

I will need to add an if loop to change all week 54 data to week 1.

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or Monday

Created:
Jan 8, 2018 2:26 PM
| Last Modified: Jan 8, 2018 2:27 PM
(7056 views)
| Posted in reply to message from JensRiege 01-08-2018

Good catch, here is a simple solution

```
Names Default To Here( 1 );
testdate = Date MDY( 12, 31, 2017 );
Week Of Year( testdate + If( Year( testdate ) == Year( testdate + In Days( 1 ) ), In Days( 1 ), 0 ) );
```

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sorry the delay in replying. Your solution works well. Thank you.

Just to make sure that the solution is clear,

@txnelson wrote:Good catch, here is a simple solution

`Names Default To Here( 1 ); testdate = Date MDY( 12, 31, 2017 ); Week Of Year( testdate + If( Year( testdate ) == Year( testdate + In Days( 1 ) ), In Days( 1 ), 0 ) );`

by adding 1 day to testdate with the In Days(1) function, Sunday = 2, Monday = 3, ... Friday = 7 and Saturday wraps around to be 1 again since there is no 8th day in a week.

I also like that you can define a variable within a formula. Very helpful!

Article Labels

There are no labels assigned to this post.