Finding rate within a column

Get Help
1

I am trying to visualize a "rate" metric, aka what percent of a certain column contains a value. Each cell in this column either contains a numerical value or is empty. I'm struggling with how to get Metabase to treat the empty cells as 0s. If I use something like Share in Summarize, the empty cells are just ignored. (e.g. Share([column] > 0) just returns "1")

I saw in another forum post that a value of 0 could be assigned to empty cells by creating a custom column =coalesce([column], 0). I did this, and it worked. However, using that custom column further hasn't worked.

E.g., I thought to create a second custom column that assigns a value of 0 for empty cells and a value of 1 for non-empty cells, then summarize by averaging that custom column:

case([coalescedColumn] = 0, 0, 1)
or
case([coalescedColumn] > 0, 1, 0)

But when I attempted to Summarize the average of this new custom column, it always returned the average as 1.

Any assistance with this would be really appreciated!

2

Hi @Kierpatel
Why can't you use Average(coalesce([column], 0)) directly as a Custom Expression in summarize?
Perhaps it would be easier if you wrote the SQL that you want to have, then it's easier to understand and provide an example in the GUI.

3

Average(coalesce([column], 0)) in summarize doesn't set non-empty cells as 1.

I'm basically trying to get a measure of (# of cells with a value)/(# of total cells), hence trying to assign a value of 0 to empty cells.

Also thanks for your quick response!

4

@Kierpatel I don't understand what you need. Please provide the SQL you would have used to generate the results you need.

5

I'm not incredibly practiced with SQL unfortunately, much less in relation to Metabase, or else this would've been a lot easier for me to reverse engineer.

I'll try to be clearer in what I'm trying to do. Let's say my column looks like this:
image

There's 5 rows in this column; 3 of the rows have a numerical value and 2 of the rows are empty. Aka, 3/5 or 0.6 of the column is non-empty. I'm trying to get that kind of rate.

6

You could use the Share() expression for that https://www.metabase.com/docs/v0.42/users-guide/expressions-list.html#share

Something like:

=share(isnull([NameOfColumn]))
7

I tried this (and a few different permutations of it), but they all returned the error message,

For input string: ""

else, the result was the same; showing the "rate" as 1, or 100%, which is incorrect.

It seems like Metabase is just...not wanting to let me treat any empty cell as a 0, no matter what?

Thanks for the help though!

8

@Kierpatel Post "Diagnostic Info" from Admin > Troubleshooting, so we know which version and database you're using.

9

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:98.0) Gecko/20100101 Firefox/98.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_322-heroku-b06",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_322-heroku",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.322-b06",
"os.name": "Linux",
"os.version": "4.4.0-1101-aws",
"user.language": "en",
"user.timezone": "Etc/UTC"
},
"metabase-info": {
"databases": [
"postgres"
],
"hosting-env": "heroku",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "13.5 (Ubuntu 13.5-2.pgdg20.04+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.42.3",
"date": "2022-03-23",
"branch": "release-x.42.x",
"hash": "33fb268"
},
"settings": {
"report-timezone": null
}
}
}

10

@Kierpatel This works fine for me on the latest release querying Postgres.
You need to include a screenshot of the error, so it is possible to have context.

11

I've figured out that the error message was resulting from trying to apply the "isnull" expression to a column with empty values, rather than null values.

However, if I take the steps I listed in my first post, e.g., setting empty values in the column to 0, I'm still not able to get the result I want.

Thanks for your involved help. I'm going to walk through exactly what I need conceptually, and hopefully that'll allow you to identify how I can achieve this in Metabase.

I have a column with some rows containing numerical values, and some columns that are empty (as in, not "0," but containing no value). I want to create a visualization metric of how many rows in that column contain something, aka "the number of rows containing something" divided by "the number of rows in total."

Thanks again for all your help. Let me know if there's anything else I can provide.

12

@Kierpatel In database language an empty cell is called null. It is very important to understand the distinction between empty and null, especially when you are dealing with string columns.

Sounds like you have a table like this:

| id | col1 | col2    | col3 | col4 |
|----|------|---------|------|------|
| 1  | 5    | random  |      |      |
| 2  | 4    |         | 5    |      |
| 3  |      | another |      |      |
| 4  |      |         |      |      |
| 5  | 8    |         | 1    |      |

So you could create a Custom Expression like this, which would give a result of 0.2, because only row 4 doesn't have any data:

CountIf(isnull([col1]) AND isnull([col2]) AND isnull([col3]) AND isnull([col4])) / Count
13

That is exactly what my table looks like! And exactly what I am trying to calculate. However, I tried your custom expression, and it's giving me a result of 0 just like every other method I've tried

14

@Kierpatel Then it's likely because your columns are not null.
I don't think I can help you further. Try using SQL instead or getting help from your database admin.

15

That makes sense. Thank you for all of your help. I am entirely new to Metabase/SQL/all of this, so thanks for being patient with me.

16

So I'm not sure if this is something you could help me with; but I went in through the SQL editor to filter out rows in the column that were null and that did filter out the rows that appeared empty. Vice versa when I filtered for IS NOT NULL. If these empty rows are actually null , is there any reason that they wouldn't be counted by the custom expression CountIf(isnull([col1]) ?

17

@Kierpatel No, it should work, but you are not providing screenshots or SQL queries, so it's not possible to know anything. If it works with SQL, then use that.