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

    How to make blank/empty cells display zeros “0" in Analytics?

    1. Last updated
    2. Save as PDF
    • Product: Alma

     

    Question

    How to make blank/empty cells display zeros “0” in an Analytics report? 

    For example, we are working on an Analytics report for Journal and eBook COUNTER usage. For the journals and ebooks that have usage for some months in a year, but not all, the months with NO USAGE currently display blank cells. I was wondering if there is a formula or condition that I can use to make those blank cells automatically turn into zeros?

    Answer

    Advised by a dedicated Alma user:

    Note: This formula only works for numerical/measurement fields. 

    1. Select the gears icon on the column you want to filter and select Edit Formula 

    Article_1.png

    1. Use this formula: IFNULL(columnname, numerical value)

    Example:
    IFNULL("Usage Data Details - Release 5"."TR_J1 - Unique Item Requests",0)

    • Make sure to keep the quotations around the “columnname” in the formula

    Article_2.png

    Then click OK at the bottom of the formula box.

    MAKE SURE YOU SAVE YOUR REPORT.

     

    1. Click on the gears icon again for that same column you added the formula to, and select Column Properties.

    Article_3.png

    1. Select Data Format and check the box next to Override Default Data Format.

    Then for the “Treat Numbers As” drop-down menu, select Custom.
    Put this in the Custom Numeric Format textbox: #,##0;-#,##0;0
    It should look like this:

    Article_4.png


    Then click OK at the bottom of the box.
    MAKE SURE YOU SAVE YOUR REPORT.

    1. You should now see zeros (0) instead of empty cells in your Analytics report.

     

     

     

    • Article last edited: 10-NOV-2022