Trend object doesn't show previous value

Get Help
1

Hello everyone, I have the following (Oracle) SQL query:

SELECT TRUNC(CURRENT_DATE, 'dd') as Data, COUNT(*) as Num
FROM "M2R"."MEDIAM_SCREENING"
WHERE TRUNC(CURRENT_DATE) = TRUNC(DATA_ESECUZIONE)
[[AND {{DESCRIZIONE_SEDE}}]]

UNION ALL 

SELECT TRUNC(DATA_ESECUZIONE, 'dd'), COUNT(*)
FROM "M2R"."MEDIAM_SCREENING"
WHERE 1=1
[[AND {{DESCRIZIONE_SEDE}}]]
GROUP BY TRUNC(DATA_ESECUZIONE, 'dd')
ORDER BY Data DESC
FETCH NEXT 2 ROWS ONLY

Which returns this table:

image

However, the trend object doesn't take into account one value:

image

Whereas I'd like to see: 0 and was 1 last day

NOTE: the same thing happens if I swap the two dates by changing the ORDER BY

What am I doing wrong?

idk if this helps, but here's the report for my MB:

{
  "browser-info": {
    "language": "it-IT",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36",
    "vendor": "Google Inc."
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.14.1+1-LTS",
    "java.vendor": "Red Hat, Inc.",
    "java.vendor.url": "https://www.redhat.com/",
    "java.version": "11.0.14.1",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.14.1+1-LTS",
    "os.name": "Linux",
    "os.version": "3.10.0-1160.59.1.el7.x86_64",
    "user.language": "en",
    "user.timezone": "Europe/Rome"
  },
  "metabase-info": {
    "databases": [
      "sqlserver",
      "csv",
      "oracle",
      "h2"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2022-03-23",
      "tag": "v0.42.3",
      "branch": "release-x.42.x",
      "hash": "33fb268"
    },
    "settings": {
      "report-timezone": "Europe/Berlin"
    }
  }
}
2

Hi @MC23

Oracle has many quirks with how the name date columns. Make sure the column is actually cast as a date.

Post the ojdbc8.jar version you are using.

And you should migrate away from H2 if you are using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

3

Thanks for the answer. I have to dig this since my only work is to write SQL and populate the dashboards, I don't have info on the Databases that are used or their version. I'll inform myself and will reply as soon as I can.

4

I think I understood where the problem lies, even if it doesn't apply to my case. I'm monitoring exams that can be taken many days apart. What I've been asked to do is to compare the exams taken today (that can be 0) with the exams taken on the "last valid day" (meaning the last day with at least one exam taken).

I played a bit with the question system rather than the SQL and noticed the following thing: the trend "by day" only work if the days belong to the same week:

image

Shows:

image

But:

image

Shows:

image

Whereas if I "view by week" I get this:

image

Which translates to:

image

However, this second case doesn't work if the days belong to the same week, since I'd only have one result:

image

Does this mean it's impossisble to do what I want? Because the hospital is closed on Saturday and Sunday, so this "today VS last valid day" would often need to compare stuff between a Monday and a Friday of the previous week. I hope these examples are clear enough.

PS: this of course has nothing to do with the fact that it doesn't work between today and yesterday since they belong to the same week, but it's just an observation for the future development of this widget I have to create.

Thanks!

5

@MC23 You are looking for this:
https://github.com/metabase/metabase/issues/11200 - upvote by clicking :+1: on the first post

1 Like
6

Thanks!