Need help making line graph with dates

Brian_Rembecky · 2025-03-24T12:57:44.080Z

Screenshot 2025-03-19 at 12.38.55 PM1702×1404 89.5 KB

Hi all

I’ve made the line graph as you can see above. The issue is that there are only data points for the 5th, 10th, 12th, and 13th. How can I modify the chart so that dates that don’t have data are defaulted to show data points at 0 (zero)?

Any help would be appreciated. Thank you!

Michael_Lynn · 2025-03-24T12:58:29.093Z

hey… welcome! Can you share the data model - possibly some sample documents? We might be better able to assist if we understand the structure of the documents.

Brian_Rembecky · 2025-03-25T10:13:12.212Z

Screenshot 2025-03-24 at 11.32.36 AM184×496 4 KB

Hi! Thanks for reaching out. We are only using two fields - ‘date’ and ‘status’. We are executing a test and recording which day it executed and whether the test passed or failed. Above is the data in the database.

Brian_Rembecky · 2025-03-25T10:13:17.110Z

67507b1e9badce15abe08c3d2289a6551ae86ee7_2_1212x10001212×1000 22 KB

This is an example of the output we are looking for. Each date should have the ‘status’ set to zero if the test didn’t execute. Is it possible to get this output?

Michael_Lynn · 2025-03-25T11:49:07.400Z

Ok… so I put this together in response… hope you find it helpful.

https://github.com/mrlynn/mongodb-community-forums-316436

1. Seed Sample Data (Python Script)

Make sure you’ve got a MongoDB Atlas cluster and pymongo + python-dotenv installed.

`requirements.txt`

pymongo
python-dotenv

`.env`

MONGODB_URI=mongodb+srv://<username>:<password>@<cluster-url>/test?retryWrites=true&w=majority

`seed_data.py`

import os
from pymongo import MongoClient
from datetime import datetime
from dotenv import load_dotenv

# Load credentials
load_dotenv()
mongo_uri = os.getenv("MONGODB_URI")
client = MongoClient(mongo_uri)

# Set database and collection
db = client["charts_demo"]
collection = db["status_log"]
collection.delete_many({})  # Clear collection for repeatability

# Sample data (with gaps in dates)
raw_data = [
    {"date": "3/5/25", "status": "passed"},
    {"date": "3/5/25", "status": "passed"},
    {"date": "3/10/25", "status": "passed"},
    {"date": "3/10/25", "status": "passed"},
    {"date": "3/12/25", "status": "failed"},
    {"date": "3/12/25", "status": "failed"},
    {"date": "3/12/25", "status": "passed"},
    {"date": "3/12/25", "status": "passed"},
    {"date": "3/12/25", "status": "passed"},
    {"date": "3/12/25", "status": "passed"},
    {"date": "3/12/25", "status": "failed"},
    {"date": "3/13/25", "status": "passed"},
    {"date": "3/13/25", "status": "passed"},
    {"date": "3/13/25", "status": "passed"},
]

# Format and insert
docs = [
    {"date": datetime.strptime(entry["date"], "%m/%d/%y"), "status": entry["status"]}
    for entry in raw_data
]
collection.insert_many(docs)
print("✅ Data inserted into charts_demo.status_log")

2. Create the Chart in MongoDB Charts

Step-by-Step:

Go to MongoDB Charts and log in
Create a new Dashboard
Add a data source: choose the charts_demo.status_log collection
Click “Add Chart” → Select Line Chart or Discrete Line
Switch to Library mode in the top bar
Paste in the following aggregation pipeline:

[
  {
    $group: {
      _id: { date: "$date", status: "$status" },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      counts: {
        $push: {
          k: "$_id.status",
          v: "$count"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      date: "$_id",
      counts: { $arrayToObject: "$counts" }
    }
  },
  {
    $densify: {
      field: "date",
      range: {
        step: 1,
        unit: "day",
        bounds: "full"
      }
    }
  },
  {
    $addFields: {
      "counts.passed": { $ifNull: ["$counts.passed", 0] },
      "counts.failed": { $ifNull: ["$counts.failed", 0] }
    }
  }
]

Click Apply.

3. Configure the Chart Layout

X-Axis: date
Y-Axis:
- Add counts.passed (Aggregate: Sum)
- Add counts.failed (Aggregate: Sum)
Leave Series blank
Customize title: “Test Results per Day with Missing Dates Shown”

Final Result

You’ll see a line chart showing:

Test result counts (passed/failed)
All days between March 5 and 13, including zero-count days

Brian_Rembecky · 2025-03-25T21:28:31.355Z

Michael, thank you so much for this solution! It’s very detailed, easy to understand, and did exactly what I needed it to. It seems that I need to learn more about aggregation, which I’ll have to find some documentation on.

Thanks again

Michael_Lynn · 2025-03-25T22:12:21.732Z

Brian,

So glad you found it helpful!!

Regards,
Mike