Analytics Evidence (Cross-Product) - Analytics Date Handling

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Created By: Igelu-Eluna Analytics

Created on: 8/17/2019

The following proposal was generated in relation to the following 3 product evidence items:

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

Report