Creating a custom retail calendar

If the out-of-the box Retail 4-5-4 calendar sample data module is not sufficient for your reporting or dashboarding needs, you can create your own retail calendar.

About this task

Use the Retail 4-5-4 calendar generator data module that is included with the Cognos® Analytics samples to generate the custom retail calendar. You can modify the start and end years of the calendar, or restate which years have only 52 weeks.

Use the National Retail Federation (NRF) 4-5-4 Calendar as a reference.

Procedure

In the Team content > Calendars > Tools folder, locate the Retail 4-5-4 calendar generator data module.
Using the Save as option, save the calendar generator data module under a different name to the location where the other calendar data modules are located, which is Team content > Calendars > Tools.

Use this copy of the calendar generator to continue with your edits.
In the Data module panel, from the table context menu , select Edit SQL table.

The table SQL is shown in the expression editor.
In the Name field, change the table name so that it reflects the new date range. For example, type Retail 454 Calendar 2016-2023.

In the Expression box, modify the table SQL as required.

Follow the steps in the comments to modify the code. For example, to add years to the retail calendar, follow steps 1 to 5.

Step 1: Set the number of years with 364 days and number of years with 371 days.
In the 2016-2022 calendar, years 2016, 2018, 2019, 2020, 2021, 2022 have 364 days, and year 2017 has 371 days, which is reflected in the following select statement:
```
select R + 1 from gen_rows where  (6 * 364 + 1 * 371) >= R
```
If changing start or end years, you must specify a proper number of years with 364 days and years with 371 days, as defined in the NRF calendar. For example, to extend the calendar to include NRF year 2023 that has 371 days, use the following select statement:
```
select R + 1 from gen_rows where (6 * 364 + 2 * 371) >= R
```
The new statement reflects that years 2016, 2018, 2019, 2020, 2021, 2022 have 364 days, and years 2017 and 2023 have 371 days.
Step 2: Specify the beginning date for the calendar.
In the 2016-2022 calendar, the January 31, 2016 is the first day of retail year 2016, which is reflected in the following select statement:
```
select _add_days ( date '2016-01-31' , R ) from gen_rows
```
If you want to use a different start date, use the date as defined in the NRF calendar. To extend the calendar to include NRF year 2023, the start date remains unchanged.

Step 3: Specify dYear (the beginning date of the current retail calendar year).

In the following case statement, each when clause represents the start date of one retail calendar year. To extend the calendar to include NRF year 2023, a new when clause is added, as highlighted in bold font in the code below:

case when D >= '2023-01-29' then cast ('2023-01-29' as date)
            when D >= '2022-01-30' then cast ('2022-01-30' as date)
               when D >= '2021-01-31' then cast ('2021-01-31' as date)
               when D >= '2020-02-02' then cast ('2020-02-02' as date)
               when D >= '2019-02-03' then cast ('2019-02-03' as date)
               when D >= '2018-02-04' then cast ('2018-02-04' as date)
               when D >= '2017-01-29' then cast ('2017-01-29' as date)
               when D >= '2016-01-31' then cast ('2016-01-31' as date)
               else null end as dYear,

Step 4: Specify dyear_PY (the beginning date of the previous retail calendar year).

In the following case statement, each when clause represents the beginning date of the previous calendar year, and add_days represents the negative number of days in the previous retail calendar year. The boldedwhen clause defines the start date for the previous year (NRF 2022) for all days in NRF year 2023.

case when D >= '2023-01-29' then _add_days ( cast( '2023-01-29' as date), -364 )
               when D >= '2022-01-30' then _add_days ( cast( '2022-01-30' as date), -364 )
               when D >= '2021-01-31' then _add_days ( cast( '2021-01-31' as date), -364 )
               when D >= '2020-02-02' then _add_days ( cast( '2020-02-02' as date), -364 )
               when D >= '2019-02-03' then _add_days ( cast( '2019-02-03' as date), -364 )
               when D >= '2018-02-04' then _add_days ( cast( '2018-02-04' as date), -371 )
               when D >= '2017-01-29' then _add_days ( cast( '2017-01-29' as date), -364 )
               when D >= '2016-01-31' then _add_days ( cast( '2016-01-31' as date), -364 )
               else null end as dYear_PY,

Step 5: Specify dyear_NY (the beginning date of the next retail calendar year).

In the following case statement, each when clause represents the beginning date of the next retail calendar year, and add_days represents the number of days in the current retail calendar year. The bolded when clause defines the start date for the next year (NRF 2024). That date is 371 days after 2023-01-29` since NRF year 2023 has 371 days.

case when D >= '2023-01-29' then _add_days ( cast( '2023-01-29' as date), 371 )
                 when D >= '2022-01-30' then _add_days ( cast( '2022-01-30' as date), 364 )
                 when D >= '2021-01-31' then _add_days ( cast( '2021-01-31' as date), 364 )
                 when D >= '2020-02-02' then _add_days ( cast( '2020-02-02' as date),  364 )
                 when D >= '2019-02-03' then _add_days ( cast( '2019-02-03' as date),  364 )
                 when D >= '2018-02-04' then _add_days ( cast( '2018-02-04' as date),  364 )
                 when D >= '2017-01-29' then _add_days ( cast( '2017-01-29' as date),  371 )
                 when D >= '2016-01-31' then _add_days ( cast( '2016-01-31' as date),  364 )
                 else null end as dYear_NY

Click OK to save the expression. Then click Save to save the data module.

You created a new retail calendar generator data module.
Create a CSV file from your retail calendar generator data module in the following way:
1. Using the new retail calendar generator data module as a source, create a list report in Cognos Analytics Reporting.
2. Drag all columns from the custom calendar table into the list.
3. Optional: Sort the TheDate column as Ascending.
4. Run the report using the Run CSV option to generate the CSV output.
5. Save the report .csv file to the location where the other calendar .csv files are saved. Current calendar source files are located in the Team content > Calendars > Source files folder.
Replace the data in the Retail 4-5-4 calendar data module with the data from the new retail calendar in the following way:
1. From Team content > Calendars, open the Retail 4-5-4 calendar data module.
2. Expand the Source view .
3. In the Sources panel, right-click the module .csv file, and select Relink.
4. Select the .csv file for the custom retail calendar to use as the new source.
Save the updated retail calendar under a different name either in Team content > Calendars, or in a different location.