Concepts → Date System Variable

About a date system variable

A date system variable references date or time information. Incorta offers numerous built-in data system variables. A date system variable is available to all users and all services. The Formula Builder gives you access to date system variables when building formula expressions. Incorta calculates a date system variable when invoked.

Invocation of a date system variable

Incorta is an Apache Tomcat web application that runs in a Java Virtual Machine (JVM) on a host machine. By default, the JVM utilizes the host machine’s configurations for date and time.

Because a host machine may exist in another time zone, there is typically a disparity in time between a web browser on a client machine and the JVM running Incorta on the host machine.

When a date system variable is used, Incorta will base the time/date information on the host machine’s designated time zone, not the user. Incorta users should be aware of this fact when creating time sensitive formulas and filters.

Note

The cluster time zone location is determined during the creation of your Incorta Cloud cluster. You can see the Cluster Region from the main menu of the Incorta Cloud Cluster.

Date system variable in a physical schema

If you create a formula column in a physical schema that utilizes a date system variable, the variable is interpreted at load time and stored in memory.



Date system variable in a business schema or dashboard

If you use a date system variable in a business schema or dashboard, the variable is calculated at the time of query.



List of Date System Variables


The following are the available date system variables within Incorta:

VariableData TypeDescriptionExample
$currentDatedateThe current date10/3/20
$currentDayintegerThe current day; displayed as day of month3
$currentMonthintegerThe current month10
$currentMonthEnddateThe last date of the current month10/31/20
$currentMonthStartdateThe first date of the current month10/1/20
$currentQuarterintegerThe numerical value of the current quarter4
$currentQuarterStartdateThe first date of the current quarter10/1/20
$currentTimetimestampTimestamp of current date and time10/7/20, 8:12:14 PM
$currentWeekintegerThe numerical value of the current week of the year40
$currentWeekStartdateThe date of the first day of the current week.9/27/20
$currentYearintegerThe current year2020
$currentYearStartdateThe first date of the current year1/1/20
$dayAgodateThe date of the previous day10/2/20
$lastMonthintegerThe numerical value of the previous month9
$lastMonthStartdateThe first date of the previous month9/1/20
$lastQuarterintegerThe numerical value of the previous quarter3
$lastQuarterStartdateThe first date of the previous quarter7/1/20
$lastWeekintegerThe numerical value of the previous week of the year39
$lastWeekStartdateThe first date of the previous week9/20/20
$lastYearintegerThe previous year2019
$lastYearStartdateThe first date of the previous year1/1/19
$monthAgodateThe date one month ago9/3/20
$nextMonthStartdateThe first date of the next month11/1/20
$quarterAgodateThe date one quarter ago7/3/20
$weekAgodateThe date one week ago9/26/20
$yearAgodateThe date one year ago10/3/19

Date system variable formatting

If you are using a date system variable as the output of a formula column, the format can be edited in the properties panel of the Analyzer. The data type of a date system variable will determine which format properties can be used.

Fiscal Calendar Support

For customers with fiscal calendars that have a fiscal offset in months, administrators can enable native fiscal calendar support. This feature simplifies the setup and consumption of fiscal calendars in Incorta and allows for the rollup of transactional data into fiscal calendars.

You can define the start of the fiscal year using the Fiscal Year option in the Cluster Management Console (CMC), under Cluster Configurations > Server Configurations > Customizations.

Once enabled, date filters and date aggregations will honor fiscal calendars. You can also apply different fiscal-calendar formats depending on the selected Date Part, such as short fiscal year (FYYY), fiscal year range (FYYY-YY), fiscal quarter (FQ#), and fiscal period (FMMM).

You can use these new variables and date parts along with the existing ones to create comparisons between fiscal and Gregorian calendars. In addition, the ago and toDate functions now support the fiscal calendar.

The following system variables are now available in Incorta to support fiscal years, quarters, and periods:

Fiscal yearFiscal quarterFiscal month
currentFiscalYearcurrentFiscalQuartercurrentFiscalMonth
currentFiscalYearStartcurrentFiscalQuarterStartcurrentFiscalMonthStart
currentFiscalYearEndcurrentFiscalQuarterEndcurrentFiscalMonthEnd
nextFiscalYearnextFiscalQuarternextFiscalMonth
nextFiscalYearStartnextFiscalQuarterStartnextFiscalMonthStart
lastFiscalYearlastFiscalQuarterlastFiscalMonth
lastFiscalYearStartlastFiscalQuarterStartlastFiscalMonthStart

The following format options are available for fiscal date parts:

Date partNew format optionsExamples
Fiscal year
  ●  Long Year > YYYY
  ●  Long Fiscal Year > FYYYYY
  ●  Short Fiscal Year > FYYY
  ●  Year Range > YYYY-YY
  ●  Fiscal Year Range > FYYY-YY

  ●  2023
  ●  FY2023
  ●  FY23
  ●  2022-23
  ●  FY22-23
Fiscal quarter
  ●  No format > #
  ●  Quarter > Q#
  ●  Fiscal Quarter > FQ#

  ●  1
  ●  Q1
  ●  FQ1
Fiscal period
  ●  No format > #
  ●  Period > M#
  ●  Fiscal Period > FM#
  ●  Short Fiscal Period > FMMM
  ●  Long Fiscal Period > FMMMM

  ●  1
  ●  M1
  ●  FM1
  ●  Apr
  ●  April