Configure Multiple Fiscal Calendars

Author: Ian Britz

Introduction

By default, Kepion comes configured with two calendar definitions: Annual and Fiscal. While the Annual Calendar definition always begins on January 1st and ends on December 31st, the Fiscal Calendar can be configured to start on a different date. If a single Application requires the use of more than one Fiscal Calendar, it's possible to configure additional calendars by adding attributes to the Time Dimension.

Example

If we examine the D_Time table in the Application Database, we can see a number of attributes, such as Year, Quarter, and Month. These are the Annual Calendar definitions. There are also a number of equivalent attributes to define the Fiscal Calendar, such as FiscalYear, FiscalQuarter, and FiscalMonth. In order to add another calendar, we simply need to add and populate additional attributes for each of these concepts.

Step 1. We can add additional attributes through the Kepion UI. Navigate to the Time Dimension and select Attributes. There are eighteen attributes that constitute the Fiscal Calendar definition, but let's just add the seven most frequently used. You can add more or less depending on your specific requirements.

Step 2. Select the Time Dimension itself and choose Define Hierarchy. Select the granularity of the Hierarchy and give it a name. Then choose the order of attributes that will define the Hierarchy.

Tip: Ensure that the lowest level of the Hierarchy you define matches the Member List you're building the Hierarchy on. For example, the Months Member List should contain an attribute for months at the lowest level.

Step 3. In SQL Server Management Studio, we can run the following script to populate the attributes we added. Make sure to make the following changes before running the script:

• Change <APPLICATION_DATABASE> to the correct Application name.
• Change the @MonthOffset variable to the offset for the new calendar.
• Change the @Schema variable to define the naming schema the calendar will use.
• Change <CALENDAR_NAME> to match the name of the attributes we created in Step 1.

Note: If you changed the number of attributes from the example above, you'll have to change the SQL script accordingly.

USE [<APPLICATION_DATABASE>]
GO

DECLARE @MonthOffset INT, @Schema NVARCHAR(10);

----------------------------- Set the start of the new Calendar here -----------------------------

SET @MonthOffset = 0; -- The offset for the new calendar start month (0 = Jan, 1 = Feb, etc.)
SET @Schema = N'FY'; -- The naming schema used, such as 'FY' for 'Fiscal Year'

--------------------------------------------------------------------------------------------------

UPDATE [dbo].[D_Time]

SET 
[<CALENDAR_NAME>Year] = (SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date])) * 10000)

,[<CALENDAR_NAME>YearName] = @Schema + RIGHT((SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date]))), 2)

,[<CALENDAR_NAME>Quarter] = (SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date]))) * 10000 + (DATEPART(QUARTER, DATEADD(MONTH, -@MonthOffset, [Date]))) * 10

,[<CALENDAR_NAME>QuarterName] = N'Q' + CONVERT(VARCHAR, (DATEPART(QUARTER, DATEADD(MONTH, -@MonthOffset, [Date])))) + N' ' + @Schema + RIGHT((SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date]))), 2)
 
,[<CALENDAR_NAME>Month] = (SELECT CONVERT(VARCHAR, DATEPART(YEAR, [Date]))) + (SELECT FORMAT([Date], 'MM')) + '00'

,[<CALENDAR_NAME>MonthName] = (SELECT LEFT(DATENAME(MONTH, [Date]), 3)) + N' ' + @Schema + RIGHT((SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date]))), 2) 
 
,[<CALENDAR_NAME>DateName] = (SELECT LEFT(DATENAME(MONTH, [Date]), 3)) + N' ' + (SELECT (FORMAT([Date], 'dd'))) + N', ' + @Schema + RIGHT((SELECT YEAR(DATEADD(MONTH, -@MonthOffset, [Date]))), 2);

Step 4. To use the new calendar, select the new Calendar Hierarchy (or relevant attribute) as a Filter in the LAYOUT tab of a Form.

Step 5. In the COLUMN tab of the PROPERTIES section, choose which attribute of the calendar you want to display.

Step 6. Now our data is aggregated by the new calendar definition.

Deployment Script

As a best practice, if you add additional calendar definitions to Kepion, you should also create a Deployment Script that will automatically repopulate the new Time Dimension attributes, in case the main calendar configuration is later changed.

Simply go to Scripts under Deployment and add a new SQL Script. We can use the same SQL code from before, to populate the database.