Fiscal Calendar table¶

A fiscal calendar is a yearly accounting period that aligns the weeks and months in a calendar year with holidays and a brand’s marketing goals to align the business for an entire calendar year. A common fiscal calendar used by brands is the 4-5-4 fiscal calendar.

A 4-5-4 calendar divides years into months using a 4 weeks - 5 weeks - 4 weeks pattern. Each week starts on a Sunday and ends on a Saturday. Each quarter has the same number of days. A 4-5-4 calendar can be useful for comparing like days for sales reporting purposes.

Add table¶

The Fiscal Calendar table is generated when fiscal calendar semantic tags are applied to a data source, after which it is available to your customer 360 database as a passthrough table.

To add the Fiscal Calendar table

From the Customer 360 page, under All Databases, select the menu for the customer 360 database, and then click Edit.
From the Database Editor, click Add Table.
Name the table “Fiscal_Calendar”.
Set the build mode to Passthrough.
From the Source Table drop-down, select the Fiscal Calendar table.
Click Next.
Add a table description. This enables a tooltip that is visible from other areas in Amperity.
From the Table Semantics drop-down, select Fiscal Calendar.
Under Version History, select Enable table version history.
Click Save.

Extend transactions¶

Extend the Unified Transactions table by joining the Fiscal Calendar table using SQL similar to:

SELECT
  rollup_uit.*
  ,fc.fiscal_year AS fiscal_year
  ,fc.fiscal_quarter AS fiscal_quarter
  ,fc.fiscal_month AS fiscal_month
  ,fc.fiscal_week_number AS fiscal_week_number
  ,fc.fiscal_week_start AS fiscal_week_start
  ,fc.fiscal_week_end AS fiscal_week_end
  ,fc.day_of_week AS fiscal_day_of_week -- optional field
  ,fc.holiday_sale_name AS holiday_sale_name -- optional field
FROM rollup_uit
LEFT JOIN  fiscal_calendar fc
ON fc.calendar_date = DATE(order_datetime)

Column reference¶

The Fiscal Calendar table is generated by Amperity when fiscal calendar semantic tags are applied to source data tables.

Column name	Data type	Description
Calendar Date	Date	The standard calendar date. Important The field to which this semantic tag is applied must also be the primary key for the table.
Day Of Week	String	The day of the week.
Fiscal Month	String	The fiscal month that is associated with the calendar date.
Fiscal Quarter	String	The fiscal quarter that is associated with the calendar date.
Fiscal Week End	Date	The calendar date on which the fiscal week ends.
Fiscal Week Number	Integer	The number of the week within the fiscal year. This field indicates on which month a fiscal year starts.
Fiscal Week Start	Date	The calendar date on which the fiscal week starts.
Fiscal Year	Integer	The fiscal year that is associated with the calendar date.
Holiday Sale Name	String	The holiday date (or date range) to which this date belongs.