Trend Visualization not working with Native Query (MongoDb)

I am creating a native query question for Trend Visualization using following MongoDB query -

[
  {
    "$project": {
      "date": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$createdAt"
        }
      },
      "totalLeads": "$totalLeads"
    }
  },
  {
    "$project": {
      "_id": "$date",
      "totalLeads": "$totalLeads"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "min_count": {
        "$first": "$totalLeads"
      },
      "max_count": {
        "$last": "$totalLeads"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "date": {
        "$dateFromString": {
          "dateString": "$_id",
          "format": "%Y-%m-%d"
        }
      },
      "leadsCreated": {
        "$subtract": [
          "$max_count",
          "$min_count"
        ]
      }
    }
  },
  {
    "$sort": {
      "date": 1
    }
  }
]

The query is returning correct data --> date and leadsCreated, but the Trend Visualization is still disabled and when clicked this error message is shown -
Group by a time field to see how this has changed over time

PFA screenshot for references. Please help me out.

Diagnostic Info -
{
“browser-info”: {
“language”: “en-GB”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4147.89 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “1.8.0_252-8u252-b09-1~18.04-b09”,
“java.vendor”: “Private Build”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_252”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “25.252-b09”,
“os.name”: “Linux”,
“os.version”: “4.15.0-1063-aws”,
“user.language”: “en”,
“user.timezone”: “Etc/UTC”
},
“metabase-info”: {
“databases”: [
“googleanalytics”,
“mongo”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.36.0-rc2”,
“date”: “2020-07-08”,
“branch”: “remove-equiv-method-implementations”,
“hash”: “5956b4d”
},
“settings”: {
“report-timezone”: null
}
}
}

Hi @ankit939
0.36.0 has been released, so you should use that instead of rc2.
You need to return a date-type, not a string, to be able to use Trend.

“date” field is date-type only. It is not a string. Used $dateFromString on date field in last $project block as you can see in the query.

@ankit939 I don’t use MongoDB, but try using the interface to generate a query that works with Trend and then compare the differences.
I know SQLite causes some problems, so perhaps it’s the same for MongoDB:
https://github.com/metabase/metabase/issues/12388

I have the same problem, leave the field in date, string format, multiple ways and nothing.
Metabase version 0.37.8

it’s a time field

group by date and quantity

@jlcornejo And if you use the interface, does that work? Can you compare the two queries to see if you can spot the problem.

with the interface it works, the problem is that I have to join 2 collections, and that is not supported by the interface

@jlcornejo Okay, then compare the queries and result data to see if you can spot the problem.

I cannot compare, when creating the trend graph from the interface and transforming to query, the functionality is lost, the trend graph is disabled

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