Max of number data type gives text data type

Get Help
1

Good day,
Hoping you are all safe and well,

I am building a medical KPI dashboard which shows what % of patients have had certain treatments done during a period of time. The period of time is selected as date range in the dashboard.

In my source table I have flagged each patient visit as a yes if the treatment was done, using numeric 1, or left NULL if not done.
In Metabase I am able to roll up the 1/NULL per visit, into a 1/NULL per patient, using MAX of the flag column. So far it works nicely as I can now see if a patient has (ever) had the treatment during the time period, and the output is still 1/NULL.

Now I want to roll up one more time to get the % of patients who have had the treatement, so I want to add up all the 1’s in the patient list. So it is SUM of (MAX of flag) then. Only problem is when I select ‘Sum of …’ in the editor, the ‘MAX of flag’ is not available to select from the list.

For interest I tried ‘Number of distinct values of …’ and this time the ‘MAX of flag’ is visible, but it has ‘Aa’ next to it so somehow the data type has changed. The original flag was numeric but the ‘MAX of flag’ is now text.

Is this normal and/or did I do something wrong?
Please help,
Regards,
chrisKH

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:79.0) Gecko/20100101 Firefox/79.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "Cp1252",
    "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": "Windows Server 2012 R2",
    "os.version": "6.3",
    "user.language": "en",
    "user.timezone": "Asia/Muscat"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "sqlserver"
    ],
    "hosting-env": "unknown",
    "application-database": "mysql",
    "application-database-details": {
      "database": {
        "name": "MySQL",
        "version": "8.0.18"
      },
      "jdbc-driver": {
        "name": "MariaDB Connector/J",
        "version": "2.5.1"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2020-05-28",
      "tag": "v0.35.4",
      "branch": "release-0.35.x",
      "hash": "b3080fa"
    },
    "settings": {
      "report-timezone": "Asia/Muscat"
    }
  }
}
2

Hi @chrisKH
Try using 1/0 instead, since I think the problem might be something to do with the NULL.
And you should be able to use SumIf(flag = 1) as well.

3

Hi flamber,
Thanks for the tips.
I changed it to 1/0 in the source table, did a sync and re-scan in Metabase, now in the preceding query I can see 1’s and 0’s (so no more NULL’s), but, unfortunately I still face the same issue when doing sum of (max of). It still thinks the max-of is a text.
Anyway, for now I worked around it by going into the SQL editor and using CAST as INT on the (max of) result, before doing the sum.
Regards,
chrisKH

4

@chrisKH I don’t know what the column type is of flag, but the Max of ... should return the same type, so I guess the problem might be there. There has been a lot of changes to how types are handled in 0.36