Showing '0' for Null Value

slgott

Summary

How do you show a 0 value in place of a blank value on an analysis?

Content

Hello,

We want to show the total number of something on an analysis (in this case Leads). If there were no Leads created on a day, we want to show '0' but instead our analysis is showing a blank value. I checked the box to "Include Null Values" and tried a case statement. It still shows blank value. See attached screen shots. Also, we need the actual value of 0 rather than just a cosmetic formatting issue. We need to be able to show the average number of Leads at the end of the analysis.

Any thoughts?

Thanks, Stephanie

GopiKAutomus

You can try "Edit Formula" for that column and use "IF-THEN" condition to replace NULL with 0.

 

User_SPR85

Hi Stephanie,

Use 'ifnull()' function to replace null with 0. You can do this by editing the formula on the field on which you want this to happen. For e.g., if the field name is 'FLD_NAME', the formula would be ifnull(FLD_NAME,0).

Thanks,

pras

slgott

Sorry. We tried that as well. It doesn't work.

slgott

Thank you. We tried this with the Case statement. See screen shot. It doesn't work. Thanks.

GopiKAutomus

Instead of using IS NULL in your Case-When statement, use  = '' and see if that works.

 

Regards,

Aaron Leggett

Hi Stephanie,

Have you tried transforming the data type? I sometimes come across similar issues and I will CAST it as an alternative datatype (VARCHAR, INT or FLOAT). Sometimes I would even transform it into a varchar and then back into an integer, so sort of like a nested cast function. I would then use this in conjunction with a CASE WHEN statement.

Alternatively, have you tried CASE WHEN "Lead Facts"."# of Leads" >= 1 THEN "Lead Facts"."# of Leads" ELSE 0 END ? This could work, but I think you might also run into the same issue.

It tends to be the logic configured in the RPD that dictates how the column behaves. For example, our Project Costing SAs track employee project costs, but only if they're active (therefore not giving us a true reflection of project costs if an employee leaves the company) We're a cloud customer, so cannot change our RPD config, but if you have an on-premises version you could ask your Oracle DBA to look at it and see if they can change it to bring back 0 rather than null.

Kind Regards

Aaron

 

Melissa-67834

I used the following expression to populate zero for employee number null values.  In the report, if employee number does not exist on the profile, 0 is returned. If employee number does exist, employee number is returned.

IFNULL("System User - Personal Information"."User Employee NO",0)

-Melissa

User_SPR85

Could it be a datatype issue? ifnull() works for me with both string and numeric data types without any additional conversion.

Thanks,

pras

Shawn McDonald

We had a similar issue with some performance titles and a dashboard. We were counting the number of employees for each rating and when no employees had that rating we got the 'No Results'  - we wanted a zero to display

We added a union to the Subject Area (the same SA and filters as the original analysis) and used the formula CAST('0' AS INT)

Hope this helps

Shakher Sharma

Can you try following and see if this works for you:

• Click on the gear icon next to column name.
• Select Column Properties.
• Go to Data Format tab.
• Select Override Default Data Format.
• Select Custom as value of Treat Number As list of values.
• Enter #,##0;-#,##0;0 as custom numeric format.