Calculate percentage of gains in Atlas Charts

[michael.kz (Michael Marcel)]

Hello!

I want to use a Chart Type: Number to display only the percentage of gains.

I have a collection with a field named “result” that is filled with “gain” or “loss”
I need to count number of docs in total and number of docs with result = “gain” to calculate this percentage.

How can I do it? I tried a lot of queries and calculated fields but nothing works. Can anyone help me to do it?

[tomhollander]

Hi @michael.kz -

This is possible but a little tricky. The secret is to use $facet which lets you fork the pipeline to calculate two different results (in this case, the number of gains, and the total number of documents). After this, we can combine the two results into a single figure:

[
  {
    $facet: {
      gains: [
        {
          $match: {
            result: "gain",
          },
        },
        {
          $count: "count",
        },
      ],
      total: [{ $count: "count" }],
    },
  },
  {
    $unwind: "$gains",
  },
  {
    $unwind: "$total",
  },
  {
    $set: {
      gainPercentage: {
        $divide: ["$gains.count", "$total.count"],
      },
    },
  },
]

You can put this pipeline into the Charts query bar, and then use the resulting gainPercentage value in the number chart.

HTH
Tom

[michael.kz (Michael Marcel)]

Thank you Tom, it works, perfect!!

@edit:

Just to take advantage of the post, is it possible to get the current date to filter without having to change the filter daily?

Like this:

[
  {
    $facet: {
      gains: [
        {
          $match: {
            result: "gain",
            created_at: {
              $gte: {
                $date: "2022-12-11T00:00:00Z"
              },
              $lt: {
                $date: "2022-12-12T00:00:00Z"
              }
            }
          }
        },
        {
          $count: "count"
        }
      ],
      total: [
        {
          $match: {
            created_at: {
              $gte: {
                $date: "2022-12-11T00:00:00Z"
              },
              $lt: {
                $date: "2022-12-12T00:00:00Z"
              }
            }
          }
        },
        { $count: "count" }
      ]
    }
  },
  {
    $unwind: "$gains"
  },
  {
    $unwind: "$total"
  },
  {
    $set: {
      gainPercentage: {
        $divide: ["$gains.count", "$total.count"]
      }
    }
  }
]

For example, instead of using fixed date in $gte: {$date: HERE_THE_DATE}.

It’s because I need two percentages, one of today’s data, another with all days.

[tomhollander]

As you probably figured out, you can’t use a normal Charts date filter since it doesn’t put it at the right point of the pipeline. However you should be able to do something like this:

{
          $match: {
            date: {
              $gt: {
                $dateTrunc: {
                  date: "$$NOW",
                  unit: "day"
                }
              }
            }
          }
        }