Replace blank/NULL values by 0

Hi! I’m creating a WBR visual with the Receive Accuracy metric. The logic for RA metric is= POs correctly received/total POs
There are some weeks where we don’t have any POs delivered in specific regions, so we have rows for that week with null values.

I want to substitute those null values by ‘N/A’. I first tried to edit format data for null values, but it’s not working. Then I tried the following calculated field formula, but it gives me error and I’m not able to save it:

However, I tried changing distinct_count({correct_rec})/distinct_count({purchase_order_number}) by ‘K’ as a test output and I could save it, but it’s not the output that I want:

Thanks,
Cris

Hey crizar,
the problem is that you try to use a string and a number as output in your calculated field.
You could try to put the else statement in a tostring function but i am not sure if it will be enough.

If this won’t work: try to separate the calculation in an different calculated field and in the ifelse you use tostring for that field.
I can try to create some pics later if it would help you.
BR
Robert

1 Like

This makes sense. But if I convert to string my output from the PO Receive Accuracy metric, the number will not be in percent and I can format it as it’s recognized as text. Any idea on how to fix that?

Quick and dirty: you could do concat(tostring(your calculation*100),‘%’)

2 Likes

This worked! Thank you so much!

Hi @crizar
could you pls solve the topic if @robert.eibers solution works.
BR

Sorry I thought I already did. Done now.
Thanks.

1 Like

Hello, I am trying to do the same to replace “black cells” with 0.00% to have a consistent format. Below is what the table looks like when I tried to do the - [concat(tostring(your calculation*100),‘%’).

Here is what I have in my calculated fields: concat(toString((sum(Defect)/sum(Defect+Clear))*100),‘%’)