Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Alma

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Alma
    3. Community Knowledge
    4. Analytics- Creating a date filter or prompt that includes null values and filters the column.

    Analytics- Creating a date filter or prompt that includes null values and filters the column.

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    No headers
    Created By: Rachel Merrick
    Created on: 10/25/2021



    The following formula was created to be used in a shelf list report which included prompts. For this use it allowed for the end user to filter down to items that have not been borrowed since a selected date while not filtering out null values (items that have never been borrowed).

    Formula used:

    IFNULL("Physical Item Details"."Last Loan Date (not In House) (calendar)",date '2000-01-01')

    The IFNULL function replaces the null value with another, we used the date “2000-01-01” but dates further in the past could also be used if more appropriate.

    This filter works for calendar dates.  For dates with a time stamp replace the value type “date” with “timestamp” in the formula.

    Using the formula in a prompt

    1. Create a column prompt for the field you want to filter

    2. Edit the formula of the Prompt For Column

    3. Enter your Column Formula and click ok

    4. Select the Operator and User Input to determine how the field will be filtered. In this example we have used is less than or equal to

     

     

    The report is then filtered to items that have a Last Loan Date selected by the user or earlier. The results will include items with the appropriate loan dates as well as items with a null value in the Last Loan Date column.

    Example

    search:

     

    results:

    Using the formula as a filter

    1. Create a filter for the column of your choice

    2.Select convert this filter to SQL and click ok

    3. Edit the filter accordingly and click ok. In this example we have used:

    "IFNULL("Physical Item Details"."Last Loan Date (not In House) (calendar)",date '2000-01-01')" <= date '2016-10-26'

    This will yield the same results as the example provided when using a prompt.

    The IFNULL function can also be used when editing columns, however if the column is displayed in the report the date the null was replaced with will be displayed rather than a blank cell.

    Feedback or suggestions
    Rachel Merrick
    Australian Catholic University
    Rachel.Merrick@acu.edu.au

     




    Report
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Analytics Evidence BESA1 - Institution Anonymization in Network or Community Zone
      • Analytics- How to create a button that redirects a presentation variable prompt search to Primo
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Community Content Type
      How To
      Content Type
      Documentation
      Product
      Alma
    2. Tags
      This page has no tags.
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved