49379_en-US

liliconcom

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?

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!

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

 

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.

 

Re: Calculating WEEKOFYEAR() for weeks that start on a day other than Sunday or MondayThank 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 !
JensRe: 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 ) );

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:

 

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