A fiscal week refers to a seven day accounting period that's defined by a business. Unlike a calendar week, fiscal weeks do not need to start on a Sunday. Although fiscal weeks afford great flexibility for data analysis, it can be difficult for businesses to decide how they define their fiscal weeks.
In this article, we will recommend a few ways to define a fiscal week and how to implement these personal accounting periods in Kepion.
Follow Calendar Weeks
When a business needs to compare data on a month-by-month basis, they may opt for a fiscal year that looks similar to a calendar year. They can, however, start on a different month (e.g., the calendar year starts on January, but the fiscal year starts in November). This setup would work best for businesses that do not necessarily need to have week-to-week data comparisons. If a business does need to compare data on a week-by-week basis, this setup would not be ideal, especially since the fiscal calendar will have partial weeks.
Use Case 1
Marianne manages one of many warehouses a shipping company owns. She wants to track PnLs (profit and loss). For her yearly report meetings, management does not want her to present data at a weekly level due to the shipping company's sizable amount of individual managers and warehouses. In addition, Marianne wants to avoid confusion about fiscal years and weeks for attendees who have limited accounting and financial knowledge. She chooses to use Calendar weeks and months so that she still has the granularity of week data for her tracking purposes, but can roll up the data into months matching the calendar year.
Start on Specific Day of Year
By default, Kepion sets the first Sunday of the year as the start of the first fiscal week. However, if a year starts before a Sunday (e.g., Friday January 1, 2021), you will have a partial week. It will appear as follows:
| Date | CalendarWeekName |
|---|---|
| Friday January 01, 2021 | W01 2021 |
| Saturday January 02, 2021 | W01 2021 |
| Sunday January 03, 2021 | W02 2021 |
| Monday January 04, 2021 | W02 2021 |
Having a partial week means the end of the fiscal year will not be the same as the end of the fiscal week (e.g. Friday December 31, 2021, which is not the end of the fiscal week). You would want to use this setup to compare weeks across years; start dates are immaterial in such comparisons.
Use Case 2
Oswald owns a luxury hotel. He wants to have weekly data to identify trends over certain days; however, he is not entirely dependent on this data because he knows the hotel gets an influx of customers during vacation and holiday seasons. When choosing how to analyze the data for fiscal weeks, he wants the weeks to align within the fiscal year for accounting and tax purposes, but Oswald also wants consistency for the fiscal week start date. Aware that the weeks and years does not always match up, he opts to remain consistent with the fiscal year start date. Oswald chooses this method because it captures data at the granularity he needs.
4-4-5
A type of fiscal week is the 4-4-5 calendar: two 4-week months and one five-week month. The 4-4-5 method can also work as a 4-5-4 or 5-4-4 cycle. Regardless of configuration, this setup splits the calendar year into 13-week quarters.
Note: The five-week month should not be compared to a four-week month because of the 25% increase in days.
In the example below, we have used 2021 to illustrate the 4-4-5 setup. Keep in mind that the fiscal months do not contain all the days expected in the calendar month or year. These days are either absent (e.g., Jan. 2) or colored light gray.
This setup makes it easy to compare fiscal weeks on a week-by-week basis. It also make the fiscal year end on the same day as the last day of the fiscal week. The 4-4-5 method can cause issues, however, as the calendar year has only 365 days. A 53rd week will need to be added on this calendar every five to six years.
Use Case 3
As a car manufacturing manager, Frederick wants to track how many parts are being created in his plant each week and compare that data to the same weeks in previous years. Originally, he was using a typical fiscal week calendar. He finds that when the partial weeks do not line up, it makes it hard to gauge his factory's productivity. Under the guidance of his financial lead, he switches to a 4-4-5 fiscal week calendar so he can easily interpret the data.
Extend Fiscal Years to Match Fiscal Weeks
Some businesses do not want partial fiscal weeks. Partial weeks can make it complicated for them to make a week-by-week comparison. As a result, they may opt to include days from the previous year or the year after to have complete weeks.
Examples of businesses that might benefit from such a setup include but are not limited to: office management, accounting, manufacturing, and warehousing companies. Here's what the calendar could look like:
| Date | CalendarWeekName |
|---|---|
| Thursday December 31, 2020 | W53 2020 |
| Friday January 01, 2021 | W53 2020 |
| Saturday January 02, 2021 | W53 2020 |
| Sunday January 03, 2021 | W01 2021 |
Although completed weeks within a fiscal year make it easier for businesses to capture productivity and income, and compare them to previous weeks, it makes yearly comparison more difficult because years will have data from two years within one fiscal cycle.
Use Case 4
Penelope is the owner of a stylish cat café. She brings multiple cats from an adoption shelter to her café in hopes that customers will adopt the cats. In collaboration with the shelter, she helps them track how many of the cats are adopted each week from her café. Penelope also frequently changes small aspects of her café because she wants the cats to feel comfortable in the space. As a result, she wants to track data by the week and does not care if some weekly data goes into next year's fiscal cycle. She feels the weekly data best captures how the layout affects both her business and the cat adoptions.
Calculations for Fiscal Week Types
Extend Fiscal Years to Match Fiscal Weeks
After you create your Application, run this script in Sequel Server Management Studio (SSMS). The script included below only updates the [FiscalWeek] values, so you may need to change other values within the D_Time table in order to get certain functions working (e.g., update other attributes to account for the one week that overlaps two years).
-- This script moves the extra week days into the first week of the next year,
-- if the next year doesn't begin with a full week.
UPDATE [dbo].[D_Time]
SET [FiscalWeek] = [FiscalWeek] + 9948
WHERE RIGHT([FiscalWeek],2) = 53
AND RIGHT(LEFT([FiscalWeek],5),4)
NOT IN (2016, 2022, 2033, 2039, 2044, 2050)
Create a Customized Script
We recommend you create a script that helps you achieve your fiscal week needs. Every business has different needs for their fiscal week definitions. We included an example below that shows how businesses can change Kepion's default fiscal week to a 4-4-5 fiscal week.
To implement a 4-4-5 setup in your Application:
1. In the Modeler, go to the All Dimensions node.
2. Select Add.
3. Enter Calendar for the Dimension Name
4. Select more than 25,000 members select for the Dimension Size.
5. Click Save.
6. Click Add New three times to create three new Members.
7. Double-click the Member to change the Member Label. Name one Member Day, another Period, and the last one Week.
8. Go to the Attributes node under your Calendar Dimension.
9. Select Add to create the following attributes:
10. Under the Deployment node, go to Scripts.
11. Select Add.
12. Enter the Script Name and set the Script Type to SQL.
13. Click Save.
14. In the Script Editor, select Edit.
15. Copy and paste this deployment script in your Application.
--FIRST CONFIGURE D_TIME FISCAL YEAR TO MATCH THE CUSTOMER FISCAL YEAR
--IN THIS EXAMPLE, WE SET FY2014 STARTING FROM 7/1/2013
DECLARE @FiscalYear_Start INT
,@FiscalYear_End INT;
SET @FiscalYear_Start = 2015;
SET @FiscalYear_End = 2019;
DECLARE @LastDay TABLE
(
FiscalYear INT
,[LastDay] DATETIME
,[LastDayID] INT
)
INSERT INTO @LastDay
SELECT FiscalYear, MAX([Date]), MAX(MemberID)
FROM D_Time DT
WHERE Granularity = N'Day'
AND DayOfWeekName = N'Saturday'
AND MonthOfYearName = N'June'
AND FiscalYear >= (@FiscalYear_Start - 1) * 10000
AND FiscalYear <= @FiscalYear_End * 10000
GROUP BY FiscalYear
DECLARE @FirstDay TABLE
(
FiscalYear INT
,[FirstDay] DATETIME
,[FirstDayID] INT
)
INSERT INTO @FirstDay
SELECT
LD.FiscalYear + 10000
,DT.[Date]
,DT.MemberId
FROM @LastDay LD
INNER JOIN D_Time DT
ON DT.[Date] = DATEADD(DD, 1, [LastDay])
WHERE DT.Granularity = N'Day'
AND LD.FiscalYear / 10000 < @FiscalYear_End
DELETE FROM @LastDay
WHERE FiscalYear / 10000 < @FiscalYear_Start
DECLARE @Base TABLE
(
[Date] [datetime],
[CalendarYear] [int],
[CalendarQuarter] [int],
[CalendarMonth] [int],
[CalendarWeek] [int],
[CalendarWeekday] [int],
[CalendarDay] [int],
[CalendarYearName] [nvarchar](25),
[CalendarQuarterName] [nvarchar](25),
[CalendarMonthName] [nvarchar](25),
[CalendarWeekName] [nvarchar](25),
[CalendarWeekdayName] [nvarchar](25),
[CalendarDayName] [nvarchar](25),
[FinancialYear] [int],
[FinancialDayOfYear] [int],
[FinancialQuarter] [int],
[FinancialDayOfQuarter] [int],
[FinancialPeriod] [int],
[FinancialDayOfPeriod] [int],
[FinancialWeek] [int],
[FinancialDayOfWeek] [int],
[SalesWorkingDay] [bit],
[FinancialDay] [int],
[FinancialYearName] [nvarchar](25),
[FinancialQuarterName] [nvarchar](25),
[FinancialPeriodName] [nvarchar](25),
[FinancialWeekName] [nvarchar](25),
[FinancialDayName] [nvarchar](25)
)
INSERT INTO @Base
SELECT
[Date]
,[Year] [CalendarYear]
,[Quarter] [CalendarQuarter]
,[Month] [CalendarMonth]
,[CalendarWeek] [CalendarWeek]
,[DayOfWeek] [CalendarWeekday]
,MemberId [CalendarDay]
,YearName [CalendarYearName]
,[QuarterName] [CalendarQuarterName]
,[MonthName] [CalendarMonthName]
,CalendarWeekName [CalendarWeekName]
,[DayOfWeekName] [CalendarWeekdayName]
,[DayName] [CalendarDayName]
,FiscalYear [FinancialYear]
,FinancialDayOfYear
,[FiscalYear] + [FinancialQuarter] * 10 [FinancialQuarter]
,ROW_NUMBER() OVER(PARTITION BY FiscalYear, [FinancialQuarter] ORDER BY DC.MemberId) [FinancialDayOfQuarter]
,[FiscalYear] + [FinancialPeriod] * 100 [FinancialPeriod]
,ROW_NUMBER() OVER(PARTITION BY FiscalYear, [FinancialPeriod] ORDER BY DC.MemberId) [FinancialDayOfPeriod]
,[FiscalYear] + 9900 + [FinancialWeek] [FinancialWeek]
,FinancialDayOfWeek
,CASE
WHEN DayOfWeekName IN (N'Saturday', N'Sunday') THEN 0
ELSE 1
END [SalesWorkingDay]
,MemberId [FinancialDay]
,[FinancialYearName]
,N'Q' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialQuarter]), 2) + N' ' + [FinancialYearName] [FinancialQuarterName]
,N'P' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialPeriod]), 2) + N' ' + [FinancialYearName] [FinancialPeriodName]
,N'W' + RIGHT(N'0' + CONVERT(NVARCHAR(2), [FinancialWeek]), 2) + N' ' + [FinancialYearName] [FinancialWeekName]
,[FinancialDayName]
FROM
(
SELECT *
,CASE
WHEN [FinancialWeek] > 52 THEN 12
WHEN ((([FinancialWeek] - 1) % 13) / 4 + 1) < 3
THEN ([FinancialQuarter] - 1) * 3 + ((([FinancialWeek] - 1) % 13) / 4 + 1)
ELSE ([FinancialQuarter] - 1) * 3 + 3
END [FinancialPeriod]
FROM
(
SELECT *
,CASE
WHEN (([FinancialWeek] - 1) / 13 + 1) > 4 THEN 4
ELSE (([FinancialWeek] - 1) / 13 + 1)
END [FinancialQuarter]
,[MonthDate] + [FinancialYearName] [FinancialDayName]
FROM
(
SELECT
*
,(FinancialDayOfYear - 1) / 7 + 1 [FinancialWeek]
,(FinancialDayOfYear - 1) % 7 + 1 [FinancialDayOfWeek]
FROM
(
SELECT DT.MemberId
,DT.[Date]
,DT.[Year]
,DT.[Quarter]
,DT.[Month]
,DT.[CalendarWeek]
,DT.[DayOfWeek]
,DT.YearName
,DT.[QuarterName]
,DT.[MonthName]
,DT.CalendarWeekName
,DT.DayOfWeekName
,DT.[DayName]
,F.FiscalYear
,ROW_NUMBER() OVER(PARTITION BY F.FiscalYear ORDER BY DT.MemberId) [FinancialDayOfYear]
,N'FY' + LEFT(CONVERT(NVARCHAR(8), F.FiscalYear), 4) [FinancialYearName]
,LEFT(DT.MemberName, 8) [MonthDate]
FROM D_Time DT
INNER JOIN @FirstDay F
ON DT.MemberId >= F.FirstDayID
INNER JOIN @LastDay L
ON DT.MemberId <= L.LastDayID
AND F.FiscalYear = L.FiscalYear
WHERE Granularity = N'Day'
) DC
) DC
) DC
) DC
TRUNCATE TABLE [dbo].[D_Calendar]
SET IDENTITY_INSERT [D_Calendar] ON
INSERT INTO [dbo].[D_Calendar]
(MemberId
,[MemberLabel]
,[MemberName]
,[Input]
,[Annotate]
,[Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName])
SELECT
CalendarDay [MemberId]
,CalendarDayName [MemberLabel]
,FinancialDayName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Day' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
UNION
SELECT
FinancialWeek [MemberId]
,FinancialWeekName [MemberLabel]
,FinancialWeekName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Week' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
WHERE FinancialDayOfWeek = 1
UNION
SELECT
FinancialPeriod [MemberId]
,FinancialPeriodName [MemberLabel]
,FinancialPeriodName [MemberName]
,1 [Input]
,0 [Annotate]
,N'Period' [Granularity]
,[Date]
,[CalendarYear]
,[CalendarQuarter]
,[CalendarMonth]
,[CalendarWeek]
,[CalendarWeekday]
,[CalendarDay]
,[CalendarYearName]
,[CalendarQuarterName]
,[CalendarMonthName]
,[CalendarWeekName]
,[CalendarWeekdayName]
,[CalendarDayName]
,[FinancialYear]
,[FinancialDayOfYear]
,[FinancialQuarter]
,[FinancialDayOfQuarter]
,[FinancialPeriod]
,[FinancialDayOfPeriod]
,[FinancialWeek]
,[FinancialDayOfWeek]
,[SalesWorkingDay]
,[FinancialDay]
,[FinancialYearName]
,[FinancialQuarterName]
,[FinancialPeriodName]
,[FinancialWeekName]
,[FinancialDayName]
FROM @Base
WHERE [CalendarDay] IN (SELECT MIN([CalendarDay]) FROM @Base GROUP BY [FinancialYear], [FinancialPeriod])
SET IDENTITY_INSERT [D_Calendar] ON
TRUNCATE TABLE [dbo].[H_Calendar_Day]
TRUNCATE TABLE [dbo].[HO_Calendar_Day]
INSERT INTO [dbo].[H_Calendar_Day]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Day'
INSERT INTO [dbo].[HO_Calendar_Day]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Day]
TRUNCATE TABLE [dbo].[H_Calendar_Period]
TRUNCATE TABLE [dbo].[HO_Calendar_Period]
INSERT INTO [dbo].[H_Calendar_Period]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Period'
INSERT INTO [dbo].[HO_Calendar_Period]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Period]
TRUNCATE TABLE [dbo].[H_Calendar_Week]
TRUNCATE TABLE [dbo].[HO_Calendar_Week]
INSERT INTO [dbo].[H_Calendar_Week]
SELECT MemberId, MemberId
FROM [dbo].[D_Calendar]
WHERE [Granularity] = N'Week'
INSERT INTO [dbo].[HO_Calendar_Week]
SELECT MemberId, ROW_NUMBER() OVER (ORDER BY MemberId)
FROM [dbo].[H_Calendar_Week]