Hey, I'm a Metabse rookie.
I got an error message when trying to use Trend visualization: Group by a time field to see how this has changed over time
here is my scripts
select TIMESTAMP(week) week,
case
when s is NULL then '0'
else s
end as s
from
(
select TIMESTAMP(h.week) week,s.s
from
(
select str_to_date(concat(yearweek(created_at), 'Sunday'), '%X%V%W') AS week, count(id) h from homeworks
where (created_at between (select date_sub(now(),interval 11 week)) and now())
group by week
) h
left join
(
select str_to_date(concat(yearweek(created_at), 'Sunday'), '%X%V%W') AS week, count(id) s from schools
where (created_at between (select date_sub(now(),interval 11 week)) and now()) and status =1
group by week
) s on h.week = s.week
) t
order by week
The table result is like the pic below, but it's wrong when I use Trend:
My Diagnostic info is:
{
"browser-info": {
"language": "zh-CN",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "5.4.0-66-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"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": "2021-01-26",
"tag": "v0.37.8",
"branch": "release-x.37.x",
"hash": "490cea7"
},
"settings": {
"report-timezone": null
}
}
}
Thanks!