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 Configuration Manager.
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, 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 from Sisense, you can also define the format through the Sisense REST API.
To define fiscal dates format though the REST API:
- In the Sisense, click Admin at the top-right of the screen.
- Click REST API Reference to view the API documentation.
- In the API Documentation, select version 0.9 on the top-right of the screen.
- Click POST/settings/system to access the relevant API settings, and then click Try It Out.
- To modify the fiscal year settings, go right into the list of permissions in the emailServer parameter, and type in the desired permission (for example, you can change first month of the fiscal year from 'jan' to 'dec').
The 'month' key determines the first month of your fiscal year. The 'format' key determines how your fiscal dates are displayed, for example:
years: fiscalFormatRestAPI + ' yyyy';
quarters: fiscalFormatRestAPI + ' yyyy Q';
months: 'MM/yyyy ' fiscalFormatRestAPI;
weeks: 'ww ' + fiscalFormatRestAPI + ' yyyy';
days: 'MM/dd/yyyy ' + fiscalFormatRestAPI;
If you set the format to 'EEE d/MMM//QQ//yp-yy', the format will 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.
- Remove everything else from the list of permissions, including the extra brackets and the extra commas.
Note: If you do not remove the other parameters, you may inadvertently revert any customizations made to other parameters back to default values. Alternatively, you can copy the current settings through the GET /settings/system endpoint and paste them into the POST /settings/system endpoint, modifying or adding the 'fiscal' object, to maintain your customized values.
- Click Execute to apply the changes.
- Scroll down to the Responses section to verify that the response has no errors.
- Once you have no errors, your change is applied.