Analytics Evidence (Cross-Product) - Analytics Date Handling
The following proposal was generated in relation to the following 3 product evidence items:
- Analytics Evidence PS4 - Add Standard Date / Time Option to All Subject Areas (Date Dimensions)
- Analytics Evidence AGSA11 (Leganto) - Date Dimensions are inconsistent
- Analytics Evidence AGSA11 (Alma) - Date Dimensions are inconsistent
The Working Group understands that existing date fields and dimensions cannot be changed to meet this new standard because of the need for backwards compatibility. This proposal is for new dates fields and dimensions created in the future.
The below date formats are proposed in order of priority. Under each field format is a list of functions and properties which can be used to generate many desired formats from the basic field content. A single date field provides only a single date field as an option. A date dimension provides an expandable list of date field options, most of which would be unnecessary if the below field formats were to be adopted.
This change is recommended because the existing date formats
- make sorting by month so complicated that it requires instructions for a beginner which are different depending on the subject area
- makes filtering (by some of the fields) more complicated
- makes it hard to find the correct month field because of the HUGE variety of options and inconsistency among subject areas
Less fields together with straightforward instructions will be easier and more constructive, because instead of all the workarounds, the user has the chance to learn to use tools which can help them in other ways.
Where Fiscal Period is NOT in use, these 5 fields could replace the current 9 or so date dimension fields.
Where Fiscal Period is in use, these 8 fields could replace the current current 13 or so date dimension fields.
Proposal
[A] For BOTH Date Dimensions AND Single Date Fields
[1] Date ONLY (no time) with date picker FOR ALL DATE FIELDS
| Desired Format | Function / Property Setting to Generate Desired Format |
| Date (yyyy-mm-dd) | Column Properties > Data Format > Custom, YYYY-MM-DD |
| Day (1-31) | OBI Function DAYOFMONTH(expr) |
| Day of week (Mon-Sun) | OBI Function DAYOFWEEK(expr) + Column Properties > Data Format > Custom, Treat Numbers as Day Name (Abbreviated) – sorts in correct order |
| Day of week (Monday-Sunday) | OBI Function DAYOFWEEK(expr) + Column Properties > Data Format > Custom, Treat Numbers as Day Name – sorts in correct order |
| Day of week number (1-7) | OBI Function DAYOFWEEK(expr) |
| Year (yyyy) | OBI Function YEAR(expr) + Column Properties > Data Format > Override Default Data Format > uncheck Use 1000’s Separator |
| Month Key (1-12) | OBI Function MONTH(expr) |
| Month (3-letter, text) | OBI Function MONTH(expr) + Column Properties > Data Format > Custom, Treat Numbers as Month Name (Abbreviated) – sorts in correct order |
| Month (text) | OBI Function MONTH(expr) + Column Properties > Data Format > Custom, Treat Numbers as Month Name – sorts in correct order |
| Quarter (Q1-Q4) | OBI Function QUARTER(expr) |
| Month in Quarter (1-3) | OBI Function MONTH_OF_QUARTER(expr) |
[B] For Date Dimensions (NOT Single Date Fields)
The above plus
[2] Date-Time – WHERE APPROPRIATE, IN ADDITION TO THE DATE WITH DATE PICKER - Resolved in Alma with the January 2020 release
Provides all of the above format options, plus
| Desired Format | Function / Property Setting to Generate Desired Format |
| Add date picker | OBI Function CAST(expr AS date) – in theory, can be used with OBI Functions BUT THAT removes the date picker |
| Hour (0-23) | OBI Function HOUR(expr) |
| Minute (0-59) | OBI Function MINUTE(expr) |
| Second (0-59) | OBI Function SECOND(expr) |
NOTE : date-time fields should always be clearly labelled as a date-time field
- not like Alma > Fulfillment > Loan Details > Due DateTime which only contains time
- not like Alma > Fulfillment > Physical Item Details > Expected Arrival Date which contains both date and time
[3] Normalized Month
Q: What is normalized month?
A: The processing would take all dates and change the day section to 01, placing that in a “Normalized Month” field, allowing all other month formats could be generated from that.
Q: Why is this needed?
A: When only Column Properties (not OBI functions) are used for month formatting, the result is separate lines for different days in a month:
2017-July | Jul | 1
2017-July | Jul | 1
| Desired Format | Function / Property Setting to Generate Desired Format |
| Full Month (sometimes written Fullmonth), MMM YY | Column Properties > Data Format > Custom, MMM YY (same as Full Month Desc) |
| Year Month (various fields, also To Year-Month and From Year-Month), YYYY MMM | Column Properties > Data Format > Custom, YYYY MMM |
| Year Month Desc, YYYY-MM | Column Properties > Data Format > Custom, YYYY-MM (same as Month Desc) |
| Year Month Key, YYYYMM | Column Properties > Data Format > Custom, YYYYMM (same as Full Month Key) |
[4] Dates Drill-down
[5] Date Filter (note that it can also be used for Fiscal Periods)
[C] For Date Dimensions which include Fiscal Period (NOT Single Date Fields)
The above plus
[6] Normalized Fiscal Month (in an October to September fiscal year, fiscal month 1 equals October), same principle as Normalized Month
| Desired Format | Function / Property Setting to Generate Desired Format |
| Fiscal Full Month (currently number, not date, and can’t be cast as date), YYYYMM | Column Properties > Data Format > Custom, YYYYMM |
| Fiscal Month Key (0-12, why does this start from 0?) | OBI Function MONTH(expr) |
| Fiscal Month (3-letter, text) | OBI Function MONTH(expr) + Column Properties > Data Format > Custom, Treat Numbers as Month Name – sorts in correct order |
It may be possible to use a normalized fiscal month as a fiscal year, but since this doesn’t exist, we can’t test it. Either way, it is possible that Fiscal Year Desc provides the needed Fiscal Year field.
[7] Fiscal Period Drill-down
[8] Fiscal Year Desc (allows user to add text description of the fiscal year, e.g., FY-2016/17)
From the IGeLU-ELUNA Analytics Working Group, Analytics Evidence Document
Please send questions or comments to the Analytics Working Group Listserv