Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Analytics Evidence (Cross-Product) - Analytics Date Handling

    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

    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