How can show a cumulativeCount graph on custom groups on looked up collection

Hi, I have 2 collection, Buyer and User. I would like to plot a graph that at each week what was the number of buyers signed up per each company. I need to merge in User to the buyers collection to filter out test users and non-verified buyers. I have the following aggregation query that returns the data that need to be plotted but cannot figure it out how to actually plot it because of 2 reasons:

  1. I need to implement cumulativeCount by myself instead of using the one provided by the charts because I am groupping with 2 fields and not one (and in that case the Periodic toggle disappears from the UI)
  2. I cannot use $lookup in a query

This is the aggregation query on Buyer collection that returns the data I would like to plot:

[
  {
    $lookup:
      {
        from: "User",
        localField: "userId",
        foreignField: "_id",
        as: "user",
      },
  },
  {
    $unwind:
      {
        path: "$user",
      },
  },
  {
    $match:
      {
        "user.emailVerified": {
          $ne: null,
        },
        "user.email": {
          $not: {
            $regex: "test",
          },
        },
      },
  },
  {
    $group:
      {
        _id: {
          week: {
            $dateTrunc: {
              date: "$createdAt",
              unit: "week"
            },
          },
          company: "$company"
        },
        count: {
          $count: {},
        },
      },
  },
  {
    $setWindowFields: {
      partitionBy: "$_id.company",
      sortBy: { "_id.week": 1 },
      output: {
        cumulativeTotal: {
          $sum: "$count",
          window: { documents: ["unbounded", "current"] }
        }
      }
    }
  }
]

Hi @zsolteszku -

While you can’t directly use $lookup in the query bar, you have to options to work around this:

  1. You can create a lookup field by choosing the option on the source field in the field panel

  2. You can add a Charts View that contains the lookup and/or the other stages in your pipeline.

HTH
Tom

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.