Choose Language Hide Translation Bar
Highlighted
JensRiege
Level III

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

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
txnelson
Super User

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

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

View solution in original post

Highlighted
JensRiege
Level III

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

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!

View solution in original post

5 REPLIES 5
Highlighted
txnelson
Super User

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

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

View solution in original post

Highlighted
JensRiege
Level III

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
Highlighted
JensRiege
Level III

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
txnelson
Super User

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

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
JensRiege
Level III

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

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!

View solution in original post

Article Labels

    There are no labels assigned to this post.