cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
JensRiege
Level IV

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

JensRiege
Level IV

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

6 REPLIES 6
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
JensRiege
Level IV

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

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.

 

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

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!

hogi
Level XI

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

Besides different days for "first of week", it's also a bit surprising, how Week of Year is calculated in general.

Especially when comparing "Week of Year" and "Year Week" (from the new formula context menu).
In 2023, Rule #3 was more than 1 week delayed compared to Rule #1:

hogi_1-1704826045502.png

hogi_2-1704826293964.png

 

New Table( "Week",
	Add Rows( 30 ),
	New Column( "date",
		Format( "m/d/y", 12 ),
		Formula( Date MDY( 1, 1, 2023 ) + (Row() - 11) * (24 * 3600) )
	),
	New Column( "\!"Year Week\!"",
		Character,
		"Nominal",
		Formula(
			Local(
				{w = Week Of Year( :date, 3 ), y = Year( :date ), m = Month( :date )
				},
				If( Is Missing( :date ),
					"",
					Char( If( m / w >= 12, y + 1, w / m >= 52, y - 1, y ) ) ||
					If( w >= 10, "W", "W0" ) || Char( w )
				)
			)
		)
	),
	New Column( "Week of Year, rule1",
		Formula( Week Of Year( :date ) )
	),
	New Column( "Week of Year, rule3",
		Format( "Best", 12 ),
		Formula( Week Of Year( :date, 3 ) ),
		Color Cells( {32, {19, 20, 21, 22, 23, 24, 25}} )
	),
	New Column( "day",
		Formula( Day Of Week( :date ) ),
		Value Labels( {1 = "Sun", 2 = "Mon"} ),
		Use Value Labels( 1 )
	)
)