Why is the trend not working?

#1

Hi,

below you can see my query it returns the date by month and I want to see a trend arrow in relation from last month to two months ago. Can someone tell me what is going wrong here?

select sum(users), date_trunc('month', date) as "Month"  from analytics_googleanalyticsusers
where date between {{startdate}} and {{enddate}} and project_id={{project_id}}
and cast(schema as int) = -1
group by date_trunc('month', date)
#2

Hi @snake-py
Please post “Diagnostic Info” from Admin > Troubleshooting, and which database type you are querying.
What do you mean with “trend not working” - what isn’t working, are you getting error, …?
I would guess that you are not returning data in format that Metabase can understand (since this is SQL instead of through the GUI).

#3

Hello @flamber

Thank you for your fast reply :smiley:

I am using 0.37.1

Well I am querying the sum of user and accumulate by month
Mas Select return two cols one of type date and one of type int. When I select the trend I want to see the change from one month to another. Currently, within in trend, I am only getting time-based options.
I also dont see any output anymore.

image
image.png1137×714 32 KB

Here is the request info
{
“browser-info”: {
“language”: “de-DE”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 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.19.0-12-amd64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“h2”,
“postgres”
],
“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”: “2020-11-12”,
“tag”: “v0.37.1”,
“branch”: “release-x.37.x”,
“hash”: “ba57ab6”
},
“settings”: {
“report-timezone”: null
}
}
}

#4

@snake-py You’re seeing this issue:
https://github.com/metabase/metabase/issues/13710 - upvote by clicking :+1: on the first post
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

#5

@flamber
I looked ath the GitHub Post but I am not sure what they mean by - group by Quantity:unbinned and CreatedAt:Month?

My quantity would be the sum of users, but I cannot group after the sum?

select sum(users), date(date_trunc('month', date)) from analytics_googleanalyticsusers
where date between {{startdate}} and {{enddate}} and project_id={{project_id}}
and cast(schema as int) = -1
group by date(date_trunc('month', date))
#6

@snake-py That does matter. It’s just steps-to-reproduce. But make sure that your date column is the first column in your select-clause.