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 

2. 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

Then click OK at the bottom of the formula box.

MAKE SURE YOU SAVE YOUR REPORT.

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

4. 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:

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

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

• Article last edited: 10-NOV-2022