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.
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;
)
);
In fear of begging AND choosing, how would you do calc fiscal month? Thank you!
That should get me until a 53 week year, and then it won't matter because the Armageddon will start. :> Thanks
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 )
)
);
