Skip to main content

Implement a 4-4-5 Calendar

Overview

The 4-4-5 calendar is primarily used for the analysis across accounting periods equally.  The same applies to the other well-known 4-5-4 and 5-4-4 calendar variants.

Process

It is strongly recommended that only users with an advanced knowledge of SQL attempt the steps contained in this article.  If this customization needs to be made to your project but you feel you cannot complete the below steps without assistance, please contact your BI Consultant or support@jetreports.com.

  1. Step 1: Add the Accounting Period Table

    For the purposes of this example, we will be using the Accounting Period table from a NAV 2015 database.

    If you are not using NAV and/or you do not have an Accounting Period table available, you will need to create something similar either in SQL or Excel.  The process described here relies on the specific month and year start dates to be in place in the Accounting Period table (or similar).

  2. To bring your Accounting Period table into your Jet Data Manager project, scroll down to your Data Sources and click the source in question.  Upon clicking on the data source, you will see the navigation pane shown on the right-hand side of the Jet Data Manager.  Scroll down to the Accounting Period table and ensure that the fields as shown below are selected (Name, New Fiscal Year, and Starting Date).

    3. Once added, locate the newly added table in your Staging database.  Right-click the table and select Deploy and Execute to continue.

  3. Step 2: Modify View Query as Needed

    Next, you will need to open up the file named SQLQuery: SQLQuery.sql.  Once opened, modify the query to fit your specific fiscal calendar and ensure that the naming conventions used in the query match your local environment (i.e. Database names, table names, etc.).

    To verify that the query will work within your environment, you can comment out the first line of the query by placing two dashes -- in front of the 'CREATE VIEW' statement.  Once the 'CREATE' portion is commented out, you will be able to execute the query locally.

  4. Step 3: Create View

    • Once you've verified that the SQLQuery file will work in your environment, the next step is to add the query into the project as a View.  To do so, right-click the 'Views' node in your data warehouse and select 'Add Custom View'.

    • In the window that opens, you will paste the code from the SQLQuery file into the entry section.  For the Name as in script, make sure to name it the same as the View creation title.  Click OK to proceed.

    • Once created, the next step is to deploy the view.  To do so, right-click the view (here, Fiscal Calendar) you just created and select Deploy.  In the window that opens, simply click Start to begin the deployment process and then click Close when the process finishes.

    • Once deployed, you will need to right-click the view once more and this time select Read View Fields.

  5. Step 4: Integrate View with Date Table

    With the view created and deployed, the next step is to begin bringing the view fields into your Date table as lookups.

    Each of these lookups will use the following as the join condition:

    1) FiscalStartDate is less or equal to Date

    2) FiscalEndDate is greater or equal to Date

    • After adding all of the view fields to your date table, the next step is to create a new custom field on the date table.  To do so, right-click your Date table and select Add Field.

    • In the window that opens, give the new field a name such as FiscalWeekNumber. You will also need to set the Data Type to Text , the Text Length to 5 , and check the Unicode box. Click OK to proceed.

    • Once created, right-click your new field and select Field Transformations.

    • In the Field Transformation pane that appears on the right-hand side, leave the Operator set to Custom and then click Add.

    • In the window that opens, you will need to paste the following code into the entry area.  If your fields are named differently, you will need to adjust the SQL code accordingly. Click OK to proceed. 

      RIGHT('0' + CAST(DATEDIFF(d, [FiscalYearStartDate], [Date]) / 7 + 1 as VARCHAR), 2)

    • Once the FiscalWeekNumber field has been created, the next step is to create two additional custom fields.  This will essentially repeat the process from the FiscalWeekNumber creation.

      To start, right-click the Date table and select Add New Field For the two custom fields, one should be named FiscalYearWeekNumber and the other should be named FiscalYearWeekName The settings for these fields should be as shown below.

      For each of these fields, you will be applying the following transformations:

      Fiscal Year Week Number

      CAST([FiscalYear] AS varchar(4)) + CAST([FiscalWeekNumber] as varchar(2))

      CAST([FiscalYear] AS varchar(4)) + ' Wk ' + CAST([FiscalWeekNumber] as varchar(2))

    • With all of the modifications to the Date table complete, right-click the Date table and select Deploy and Execute.

  6. Step 5: Create Additional Dimension Levels

    The rest of this process will be conducted within the Cubes tab of the Jet Data Manager.  To add the new Fiscal elements to your Date dimension, right-click the Date dimension and select Add Dimension Level.

    You will be creating seven additional dimension levels.  Use the images below as a template.

  7. Step 6: Set Dimension Level Types

    For each of these 7 dimension levels that you just created, you will need to set the Type of the dimension level so that the Hierarchy will behave correctly. To do so, right-click any of the Fiscal Dimension Levels just created and select Edit Dimension Level

    From within the window that opens, complete the following steps:

    The step numbers listed below correlate to the numbers within the image below.

    1. Click Advanced.
    2. Click the ellipses button '...' .
    3. Select the appropriate Dimension Level Type (listed below).
    4. Click OK.
    5. Click OK.
    6. Click OK.

    Correct Dimension Level Types :

    • Years
      • Fiscal Year
    • Quarters
      • Fiscal Quarter & Fiscal Year Quarter
    • Months
      • Fiscal Month & Fiscal Year Month
    • Weeks
      • Fiscal Week & Fiscal Year Week

  8. Step 7: Assign Dimension Level Keys

    The next step is to go back through the dimension levels once more and assign a two-part Dimension Level Key

    To start, right-click one of your Dimension Levels and select Edit Dimension Level

    In the window that opens, first click the ellipses button '...' which will open up the multi-part Dimension Level Keys selector.  Once opened, scroll down until you locate Fiscal Year and click the box next to it.  Click OK within both opened windows to proceed.

    You will need to set the two-part Dimension Level Keys for each of your new Fiscal date dimension levels before proceeding.

  9. Step 8: Create Dimension Hierarchy

    • Now that you've created and setup the additional dimension levels for your Fiscal calendar, the next step is to create a hierarchy for these values.  To do so, right-click your Date dimension once more and select Add Hierarchy.

    • In the Hierarchy window, start by giving your new hierarchy a name.  For this example, our new hierarchy will be named 'Fiscal YQMWD' (YQMWD = Year/Quarter/Month/Week/Day)

    • After naming your hierarchy, the next step is to select the dimension levels that you want contained within the hierarchy.  Upon clicking on a dimension level, it will be added to the hierarchy list in the right-hand pane.

    • Once you've added all five dimension levels as shown below, click OK to proceed.

      The order in which the dimension levels appear within your hierarchy is important.  If the ordering of yours differs from that above, simply click-and-drag the level into the appropriate location.

    • The last step in updating your Date dimension requires you to assign the Dimension Level Relations To do so, right-click your Date dimension and select Advanced > Dimension Level Relations.

    • Once in the Dimension Level Relations window, you will simply click-and-drag items and drop them onto their child element.

    • To start, click and drag Fiscal Month and drop it onto the Fiscal Week element.  Continue this process by dragging Fiscal Quarter onto Fiscal Month , and finally Fiscal Year onto Fiscal QuarterWhen finished, click OK to proceed.

  10. Step 9: Add Scope Statement (Optional)

    If you only want the 4-4-5 Calendar displayed, delete the standard 'Date YQMD' hierarchy and fix every time dependent measure by selecting the appropriate hierarchy.

    The last step in implementing your 4-4-5 Calendar deals with displaying any time sensitive measures (i.e. YTD, MTD, etc.) correctly when you have your new Fiscal hierarchy in use.

    To accomplish this, we need to create a Scope statement that will tell Analysis Services to calculate the measures differently anytime the Fiscal hierarchy is in use.

    This process will need to be completed for each time-sensitive measure that exists within your cubes.  It is recommended that you name your Script Commands in such a way that you can identify them easily (i.e. Scope_INSERTMEASURENAME).

    To start, expand the desired cube and right-click Script Commands and select Add Script Command.

    In the Add Script Command window, assign an identifiable name for the scope statement and then paste the following code into the script command window. Click OK to proceed.

    The following sample code will need to be adjusted if used for any measures other than 'Sales Amount YTD' or any hierarchies named differently than 'Fiscal YQMWD'.

    Sample Scope Statement for Sales Amount YTD

    Scope(Descendants([Posting Date].[Fiscal YQMWD],,After));

    [Measures].[Sales Amount YTD] = Sum(Ytd([Posting Date].[Fiscal YQMWD].CurrentMember), [Measures].[Sales Amount]);

    End Scope;

  11. Step 10: Use your new 4-4-5 Calendar

    The last step is to use your new Fiscal Dates and Hierarchy in a PivotTable.

Was this article helpful?

We're sorry to hear that.