topic Re: 5-4-4 fiscal calendar, col formula or jsl script in Discussions

5-4-4 fiscal calendar, col formula or jsl script

Devin — Sat, 10 Jun 2023 20:47:09 GMT

Has anyone written a column formula or script that will allow me to convert a date:time to a particular week or quarter in my company's OBSCURE fiscal calendar, known as the "5-4-4" fiscal calendar? I see google references to other fiscal calendars, but apparently the 5-4-4 is uncommon.

Re: 5-4-4 fiscal calendar, col formula or jsl script

ih — Fri, 15 Oct 2021 21:18:01 GMT

From what I understand of that system you could just find the number of days from the start of a given year and then calculate the week and quarter based on the number of days and length of each quarter. You might need to take into account 53 week years, but this should get you started:

New Table( "Fiscal Week",
	Add Rows( 1000 ),
	New Column( "Date", Numeric, "Continuous", Format( "yyyy-mm-ddThh:mm:ss", 19, 0 ),
		Input Format( "yyyy-mm-ddThh:mm:ss", 0 ),
		Formula( Date DMY( 4, 1, 2021 ) + Row() * In Hours( 12 ) )
	),
	New Column( "Fiscal Year", Numeric, "Continuous", Format( "Best", 12 ),
		Formula(
			Floor( (:Date - Date DMY( 4, 1, 2021 )) / In Days( 52 * 7 ) ) + 2021
		)
	),
	New Column( "Fiscal Day of Year", Numeric, "Continuous", Format( "Best", 12 ),
		Formula(
			Floor( Mod( (:Date - Date DMY( 4, 1, 2021 )) / In Days( 1 ), 52 * 7 ) )
			 + 1
		),
		Set Display Width( 67 )
	),
	New Column( "Fiscal Week of Year", Numeric, "Continuous", Format( "Best", 12 ),
		Formula( Floor( (:Fiscal Day of Year - 1) / 7 ) + 1 ),
		Set Display Width( 85 )
	),
	New Column( "Fiscal Quarter", Numeric, "Continuous", Format( "Best", 12 ),
		Formula( Floor( (:Fiscal Week of Year - 1) / 13 ) + 1 ),
		Set Display Width( 65 )
	)
);

Re: 5-4-4 fiscal calendar, col formula or jsl script

Devin — Tue, 19 Oct 2021 15:54:26 GMT

That should get me until a 53 week year, and then it won't matter because the Armageddon will start. :> Thanks

Re: 5-4-4 fiscal calendar, col formula or jsl script

Devin — Tue, 19 Oct 2021 17:11:46 GMT

In fear of begging AND choosing, how would you do calc fiscal month? Thank you!

Re: 5-4-4 fiscal calendar, col formula or jsl script

ih — Wed, 20 Oct 2021 11:38:50 GMT

Well, if the week of the year is 1-5 it is the first month of the first quarter, if the week of the year is 23-26 then it is the sixth month (third month of the second quarter), etc. A big if statement would do the trick. That said, here is another way:

New Column( "Fiscal Month", Numeric, "Continuous", Format( "Best", 12 ),
	Formula(
		Week of Quarter = Modulo( :Fiscal Week of Year - 1, 13 ) + 1;
		Month of Quarter = If(
			Week of Quarter <= 5, 1,
			Week of Quarter <= 9, 2,
			3
		);
		Month of Quarter + (:Fiscal Quarter - 1) * 3;
	)
);