The year function extracts the year of a date as a four-digit numeric value. Once extracted, the year can then be utilized as a parameter for record selection and summarization on an annual basis.
- year(‘Date Field’)
- year is the function keyword. This must be entered into the Ninox function editor in all lower-case characters
- ‘Date Field’ is a reference to a valid Date field in a Ninox table
- Date is a hard-coded date in MM/DD/YYYY format
Things to keep in mind when constructing this function:
- Date fields are used to stamp the day, month and year that an event did, or is scheduled to, take place. Users often use Ninox to analyze events on an annual basis. To perform this analysis, it is valuable to have a parameter in the data model (schema) of the Ninox table itself that is equal to the year of the date field(s) in question. The contents of that formula / parameter field is returned from the year() function.
- When using the year() function in this context, you can create arrays using a select statement with a where” clause. An example of this is: select ‘Name of Table’ where ‘Name of Field’ = YYYY. In this context, ‘Name of Field’ = YYYY refers to a clause in the select statement that specifies that the field in the table that is serving as the year value should equal a defined year represented by YYYY. For example, we may have a field in a table of customer invoices called ‘Invoice Date’. A second formula field in the same table would be named ‘Invoice Year’. The calculation in the ‘Invoice Year’ formula field would be year(‘Invoice Date’) and would return a four digit numeric value equal to the year of the invoice date itself. Returning to the select statement, we then could write a line of code as follows: let i := select ‘Customer Invoices’ where ‘Invoice Year’ = 2016. This line would return an array (subset) of records from the ‘Customer Invoices’ table where the sole qualification to be included in the subset is that the field ‘Invoice Year’ contain the numeric value 2016. We then could apply a variety of array functions to the array stored in the variable i to derive information out of the raw data that exists in the array itself.
year(‘Transaction Date’) where the ‘Transaction Date’ field contains the value 12/18/1977 returns the value 1977