Calculated columns - How can I do custom column = Calculated field 1 divided by Calculated field 2?

Get Help
1

Hi
Im a new metabase user - love the product

I have a table with records of items sent, and a field that is set to a date if the item is accessed by user.
I can do various group by’s to show 2 columns - (count), and (countif accessed by date is >0)

  • good
    How do I add a 3rd column which shows the percentage accessed
    ie. (countif accessed by date is >0 ) / count *100

eg: How do I create the “% accessed column” in the example below (data grouped by Branch - 1 row for each item sent) - my current question built has the 1st 3 columns but cant figure out how to add the 4th column
Branch. Items Sent. items accessed. % accessed
xxxx. 1000 500 50%
yyyy 2000 200 10%
etc.
Thanks in advance !!

2

Hi @Robbo
Please post “Diagnostic Info” from Admin > Troubleshooting.
Can you post the actual Custom Expression you’re using?
I don’t see why it shouldn’t be possible.

3

hi Thanks - i'm new to metabase

so have a :question with a few joins
then have a "Summarise"
"Bundles Sent" = count
"Bundles Opened "= countif ([page visited at] >0). ,,, this set to a date or null
by
Branch

so sent = eg: 100; accessed = eg: 50
so need another column to dispkay bundles opened / bundles sent
so can define it (see screen shot)

05%20pm
05%20pm1964×1274 218 KB

but when I "Visualise: i get an error
54%20pm
54%20pm1580×938 57.5 KB

4

I have a similar issue.
When I used v0.36.x the question worked, with the update to 0.37.x, the return is "Unknown column 'Ponto de Acesso.bairro' in 'field list'".

My join is with another saved question (Caixas por Bairro - Fco. Beltrão).

image
image1322×776 31.6 KB

My diagnostic info:

{
  "browser-info": {
    "language": "en",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.193 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.7+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.7",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.7+10",
    "os.name": "Linux",
    "os.version": "3.10.0-1062.12.1.el7.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "9.6.20"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-11-12",
      "tag": "v0.37.1",
      "branch": "release-x.37.x",
      "hash": "ba57ab6"
    },
    "settings": {
      "report-timezone": "America/Sao_Paulo"
    }
  }
}
5

@Robbo You are seeing this issue, which is fixed in 0.37.1:
https://github.com/metabase/metabase/issues/12762

@yuriandreoli You are seeing this issue:
https://github.com/metabase/metabase/issues/13649 - upvote by clicking :+1: on the first post

6

Thanks @flamber :pray:

7

Thank you @flamber ! - so I simply need to upgrade to 37.1 ?
Cheers

8

@Robbo From the screenshot you have provided, then yes.

9

@flamber Looks like I didnt give you all the info ? I;ve updated to actually latest is 37.2 not 37.1 - edited existing question to add %opened = bundles sent/bundles open * 100 (note that bundles sent is count, bundles open is countif (field >0)) - see screen shots
still get same error "Unknown column 'source.Bundles Sent' in 'field list'"! Hope you can solve . DO i need to rebuild the question again now that I have upgraded ?? maybe that will fix ?Thx
Neil

06%20am
06%20am1201×580 35.2 KB
57%20am
57%20am1178×440 26.3 KB
51%20am
51%20am1147×442 20.9 KB

10

Here is the Diagnostic Info from Admon/Troiubleshooting

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.7+10",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.7",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.7+10",
    "os.name": "Linux",
    "os.version": "4.14.198-152.320.amzn2.x86_64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "h2"
    ],
    "hosting-env": "elastic-beanstalk",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.3"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.8"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-11-16",
      "tag": "v0.37.2",
      "branch": "release-x.37.x",
      "hash": "25e5f70"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
11

@flamber I think I figured out what I was doing wrong - I've now added that calculated field into the Summarize - and it works there !

22%20am
22%20am1169×680 44.3 KB

12

57%20am

13

@Robbo Okay, so you were using the Custom Expression from the metric (Summarize) in the Custom Column - there’s an issue for that, which is most likely a duplicate of an existing issue:
https://github.com/metabase/metabase/issues/13857 - upvote by clicking :+1: on the first post