By default, Sisense calculates the beginning of the fiscal year as January 1st. In some cases, you might need to express your company’s date fields according to another fiscal month, such as April 1st. Sisense allows you to easily shift your fiscal calendar to start at a different month according to your company’s requirements.
This can be defined at the system level, by the Administrator, and also at the ElastiCube level, by the Data Designer. This means that you can support one fiscal calendar month for your system, such as January 1st, while a specific ElastiCube can be set according to the US tax year which begins October 1st.
When you change the fiscal calendar, this affects how time is calculated in your dashboard and you are also getting an indication, for example when filtering your widgets and dashboards by time, through the FY tag.
|Year||Changes the calendar year to fiscal. By default, the name reflects the year in which it ends. For example, if the fiscal year starts in April, the year for the date June 1, 2014 would be shown as FY 2015.|
|Quarter||Changes the calendar quarter to that of the quarter in the fiscal year. For example, if the fiscal year starts in April, the quarter for the date June 1, 2014 would be Q1.|
|Month||Not affected by fiscal year settings. The calendar month is the same as the fiscal month.|
|Week||Changes the calendar week to the week of the fiscal year. For example, if your fiscal year begins April 1st, April 3rd would fall under Week 1.|
|Day/Minute/Hour||Not affected by fiscal year settings.|
To set your fiscal year on the system level, see System Settings.
Administrators and Data Designers can set the fiscal year on the ElastiCube level.
To set your fiscal year on the ElastiCube level:
- In the Sisense Web Application, select Admin > Data Sources.
- For the relevant ElastiCube, select the ElastiCube menu > Fiscal Year Start, and then select the month.
Formatting Fiscal Dates
When you apply fiscal years, the default format for your dates is 2018 FY for years or Q1 2018 FY for quarters.
You can customize the format of the date through placeholders such as “y” and “p” where “y” represents the year and “p” the previous year.
The examples below illustrate how you can format your dates:
- 2017/18: yyyyp/yy
- 17/18: yp/yy
- FY17/18: FY yp/yy
- Q1 2018 FY: Q yyyy FY
By default, Sisense applies FY at the end of the year when fiscal years are applied. However, if you make any changes, Sisense will no longer add the FY automatically, so you must define this with the placeholder FY in the location you want as shown above.
To format your fiscal dates:
- On the Dashboard, click the Pencil (Edit) button that appears in the top-right corner of a widget with a date dimension.
- Select the Calendar icon to define your formatting.
- Select the relevant time period and enter the relevant placeholders in the format you want to be displayed in your widget. A preview of the custom format is displayed below.
- Click OK. The format of your dates is updated in the widget.
Formatting Fiscal Dates through the REST API
In addition to formatting Fiscal dates through Sisense, you can also define the format through the Sisense REST API.
The settings/system endpoint contains the Fiscal object that has two keys, month and format.
The month key determines the first month of your fiscal year. The format key determines how your fiscal dates are displayed.
years: fiscalFormatRestAPI + ' yyyy';
quarters: fiscalFormatRestAPI + ' yyyy Q';
months: 'MM/yyyy ' fiscalFormatRestAPI;
weeks: 'ww ' + fiscalFormatRestAPI + ' yyyy';
days: 'MM/dd/yyyy ' + fiscalFormatRestAPI;
If you were to set the format to the following, 'EEE d/MMM//QQ//yp-yy', the format would be displayed in Sisense as follows:
FY Mon 1/Apr/Quarter 2/96-97 1997 Q2
In addition, you can add strings to the format as long as they are in single quotes.
To modify your fiscal date formats through the REST API, you can send a POST request to the settings/system API.