AX1511

Using date parts in Axiom queries

When reporting on Date or DateTime columns in Axiom queries, you can optionally use a date part instead of returning the full date or date-time value stored in the column. Date parts allow you to extract and use a specific part of the date or date-time value, such as Year, Month, Quarter, Hour, and so on.

When you use a date part, then for the purposes of the report you are effectively changing the column data type and contents to match the selected date part. For example, if you specify the date part as Month, the column is now treated as if it contains values from 1 to 12 representing each month. All of the other information about the date or date-time value is ignored. This means that you can use the column as a sum by column for the query, and the values will be grouped by month instead of by the underlying date or date-time value. You can also sort or filter the query by the date part.

Another advantage of date parts is that they support the concept of a fiscal year calendar. For example, you can use date parts to return either the calendar month or the fiscal month, if they are different for your organization.

Date part syntax

To use a date part, place the following syntax in the field definition of the Axiom query:

AxDatePart(ColumnName,DatePartName):SpecialColumnName

• ColumnName is the table.column name, such as Encounter.AdmitDate.

• DatePartName is the name of the date part to return, such as Year, Month, YearMo, or Quarter. See Available date parts for the full list of date parts.

• SpecialColumnName is an optional defined name for the transformed column, so that you can reference it in the sum by, data sort, or data filter for the Axiom query. The special column name (and the preceding semicolon) can be omitted if you do not need to reference the column elsewhere.

The following example shows date part syntax used in the field definition of an Axiom query. In this example the column is EncounterEDS.AdmitDate, the date part being used is YearMo, and the special column name is defined as AdmitYearMo. The AdmitYearMo special column name is being used to sum the Axiom query by YearMo, and also to sort and filter the query.

Example Axiom query using date part syntax

Using date parts in Axiom query settings

Once the date part syntax has been defined in the field definition, the special column name can be used in the following settings to sum, sort, and filter the query by the date part:

• Sum By

• Data Filter

• Data Sort

• Data Range Filter (the filter on the AQ tag to filter a particular block)

• Sheet Filter

The special column name is required in order to use the date part in any of these settings. If you use the regular table.column name instead of the special column name in these settings, then the setting will use the full date or date-time value instead of the date part.

NOTE: If the special column name is used in a Sheet Filter, it will only apply to the Axiom query where the date part syntax is used—it cannot filter all queries defined on the sheet. However, using the Sheet Filter allows you to filter the query by a specific table or table type, which is not possible when using the regular Data Filter.

Combining date part syntax with other special features

You can use date part syntax with other special field definition syntax such as alternate aggregations and column filters. However, if the date part uses an alternate aggregation or a column filter, then the special column name can only be used to define the Data Sort—it cannot be used in the Sum By or any of the filter settings. Therefore if you do not want to sort by the column, you can omit the special column name (and its preceding semicolon).

Although date part syntax is compatible with the majority of Axiom query features, there are some limitations:

• Drill through does not work when a date part is used in the sum by.

• Although drill down works with date parts, if the date part is used in the sum by and one of the groupings is for blank values, the blank value row cannot be drilled.

Available date parts

The following tables detail the available date part options for Date and DateTime columns in Axiom queries. Note that the date part feature for Axiom queries does not include any format options. For example, when using the Month date part, the return values are always 1-12—there is no built-in option to return a different format such as Jan, 1-Jan, or January. However, you can use spreadsheet features such as cell formatting or formulas to transform the return values as desired.

Standard date and time options

Date Part Name	Description	Return Values
Date	Use the date part of the date-time. Only applies to DateTime columns.	Date serial number (basically, treats the DateTime column as a Date column)
YearMo	Use the combined year and month of the date.	Year and Month combined (for example: 202210)
Year	Use the year part of the date.	Full year (for example: 2022)
Quarter	Use the quarter for the date.	Number of the Quarter (1-4)
Month	Use the month part of the date.	Number of the Month (1-12)
Week	Use the number of the week for the date, within the year.	Number of the Week (1-52)
DayofYear	Use the day of the year for the date.	Number of the Day (1-365)
DayofMonth	Use the day of the month for the date.	Number of the Date (1-31)
DayofWeek	Use the day of the week for the date. The first day of the week is Sunday.	Number of the Day (1-7)
Hour	Use the hour of the date-time. Only applies to DateTime columns.	24-Hour Clock Number (0-23)
Minute	Use the minute of the date-time. Only applies to DateTime columns.	Number of the minute (0-59)

Fiscal year options

The fiscal year for your system is determined by the system configuration setting ClientFiscalYearEndMonth. By default, this is set to 12, which means the fiscal year is the same as the calendar year. If your organization uses a different fiscal year end, your implementation consultant should adjust this setting accordingly. For more information on system configuration settings, see System configuration settings.

For example, if your organization's fiscal year ends in June, the ClientFiscalYearEndMonth setting should be changed to 6. This means:

• A date of 6/1/2022 is in fiscal year 2022 and represents month 12 of the 2022 fiscal year.
• A date of 7/12022 is in fiscal year 2023 and represents month 1 of the 2023 fiscal year.

When the fiscal year is different than the calendar year, the fiscal year options will return different date information than the corresponding standard date options. Continuing the example where the fiscal year end is June, the following return values apply to a date of 7/1/2022:

• The Year part will return 2022, whereas the Fiscal Year part will return 2023.
• The Quarter part will return 3, whereas the Fiscal Quarter part will return 1.
• The Month part will return 7, whereas the Fiscal Month part will return 1.

Date Part Name	Description	Format
FiscalYearMo	Use the combined fiscal year and month that the date belongs to.	Year and Month combined (for example: 202310)
FiscalYear	Use the fiscal year that the date belongs to.	Full year (for example: 2023)
FiscalQuarter	Use the fiscal quarter that the date belongs to.	Number of the Quarter (1-4)
FiscalMonth	Use the fiscal month that the date belongs to.	Number of the Month (1-12)