Calculate percentage betwwen two months

Working with Data Drivers
1

I would like to calculate the percentage increase of some data in the database between two months. But as much as I think about it, I can’t find the aggregate.

Id: 12548
name: jhon
expence: 200$
day: 22-3-2022

id: 15426
name: mary
expence: 150$
day: 15-4-2022

more or less
calculate increase percentage between two month

{
    $setWindowFields: {
      partitionBy: {“$month”:”$day”},
      sortBy: { day: 1 },
      output: {
        sumExpence: {
          $sum: "$expencel",
          window: { documents: [ -1, 0 ] }
        },
        previousDateTime: {
          $push: "$date",
          window: { documents: [ -1, 0 ] }
        }
      }
    }
  }

Something idea??
thank you

2
  1. Set your sortBy to date (and don’t partition by date, or each partition won’t have historical data to compare to)
  2. Use the the range and unit window attributes to fetch a value from 30 days ago.
  3. Have the window field just return the historical value from one month ago.
  4. In a subsequent aggregation step, calculate the percentage change
[
{
    $setWindowFields: {
      sortBy: { day: 1 },
      output: {
        thirtyDaysAgoDate: {
          $last: "$date",
          window: { range: [30 * 24, 31 * 24], unit: 'hour' }
        },
        thirtyDaysAgoValue: {
          $last: "$expencel",
          window: { range: [30 * 24, 31 * 24], unit: 'hour' }
        }
      }
    }
  },
{
  $set: {thirtyDayChange: {$divide: [{$subtract: ['$totalValue','$thirtyDaysAgoValue']}, '$totalValue']}}
}
]
  • range of [30 * 24, 31 * 24] is saying to get values from the beginning of the 30th day to the beginning of the 31th day. For this to be 100% accurate the day field should be a truncated date or a timestamp that starts exactly at the beginning of the day. Then we use $last to get the last value from the range, using $last vs $first only matters if there are multiple values for a given day.
1 Like