How to assign a value of zero to null/empty fields

1

@flamber This is kind of a continuation of the previous topic you were helping me with but I'm trying to attack it from a separate direction in hopes of success.

I tried searching with no luck so I figured I'd make a thread and ask. I have a field that is either empty or has a numerical value >0. Here's a screen shot:
image
I'm trying to find a way to make those empty fields report back a zero instead of just being empty.
I've tried doing a custom column where I used the column +0 but it didn't work. I also tried =concat("0",[column]) and no dice.

My goal is to be able to show all the lines of users that have less than 3 including the empty ones so I was thinking if I could make the empty fields equal to zero, that would allow me to use a filter (less than 3) that would give me my solution.

I did find a false fix of editing the column and using a prefix of "0" which looked good on the surface but I couldn't filter like I need to.

2

Hi @paintbb84
Try sum(coalesce([field], 0))

3

it gives me the following error:

image
image1759×124 12.4 KB

4

@flamber its the "sum" function that's causing the issue. I just tried it with just coalesce and I think it worked!! You're the best!!!

5

@paintbb84 Well, you cannot use sum() on a Custom Column, but you can use it for a metric Custom Expression. Again, this depends on your data and how you're constructing your question, which I have absolutely no context of with the limited screenshots provided.

6

I completely understand and I apologize for not including enough data. But you pulled me through. I'm very grateful for your help!!