Help with a profit chart - some fields are "null"

[Jose_Olaizola]

Hi everyone, I’m very new to Mongodb, and I been getting familiar with the charts, queries and aggregations. I want to create a profit chart that will have a simple formula (Revenue - Cost).

But, not all documents have a Revenue field, so I guess they have a null value for Revenue, I guess I need to convert the null values = “0”. I need this to be grouped by day in the chart. This is the code I was trying to use, but, it was not returning anything in the chart. the field “profittest” is being create but with no values it seems. Appreciate any help in advance

[
{
    $match: { 
      $Revenue: { $ne: null },
      $Cost: { $ne: null },   
    },
  },
  {
    $group: {
      _id: {},
      TotalRevenue: { $sum: "$Revenue" },
      TotalCost: { $sum: "$Cost" }
    }
  },
  {
    $set: {
      Profittest: { 
$subtract: ["$TotalRevenue", "$TotalCost"] 
       }
	
    
      }
  }
]

[Jason_Tran]

Hi @Jose_Olaizola,

Do you have some sample documents you could provide as well?

@Jose_Olaizola

{
    $match: { 
      $Revenue: { $ne: null },
      $Cost: { $ne: null },   
    },
  }

Just to double check too, are the initial actual field names literal '$Revenue' and $Cost' (with the $ at front?).

Regards,
Jason

[Jose_Olaizola]

Hi @Jason_Tran, here is the sample documents, some of them don’t have Cost value, some others don’t have Revenue Value. the fields are called “Revenue” and “Cost” is the “$” not needed?

{
  "_id": {
    "$oid": "657a5ee6da7f76effe0c042a"
  },
  "fields": {
    "Entry Date": "2023-12-14T01:48:22.000Z",
    "Cost": 30,
    "Revenue": 1225
  }
}

{
  "_id": {
    "$oid": "655e0ff79ed4ae1e357235c6"
  },
  "fields": {
    "Entry Date": "2023-11-06T22:40:02.000Z",
    "Revenue": 1125
  }
}

{
  "_id": {
    "$oid": "655d2e1f8b5ac7a0dff5db95"
  },
  "fields": {
    "Entry Date": "2023-11-21T22:24:31.000Z",
    "Cost": 0.75
  }
}

Thanks in advance

[Jason_Tran]

Hi @Jose_Olaizola,

Based off your sample documents you’ve provided, it appears Cost and Revenue are within another field named "fields".

Let’s say I have the following documents (copied from what you provided):

db.collection.find({},{_id:0})
[
  {
    fields: {
      'Entry Date': '2023-12-14T01:48:22.000Z',
      Cost: 30,
      Revenue: 1225
    }
  },
  {
    fields: { 'Entry Date': '2023-11-06T22:40:02.000Z', Revenue: 1125 }
  },
  { fields: { 'Entry Date': '2023-11-21T22:24:31.000Z', Cost: 0.75 } }
]

If I try perform the $match with for a field named "$Revenue" (based off the above documents):

db.collection.aggregate({$match:{'$Revenue':{'$ne':null}}})
Uncaught:
MongoServerError: unknown top level operator: $Revenue. If you have a field name that starts with a '$' symbol, consider using $getField or $setField.

I assume you’re wanting the resulting document to be something like the below (correct me if I am wrong in my assumption here):

{
   "_id" : null,
   "Profittest": 2319.25
}

Calculated that as just using the "fields.Revenue" values summed minus the "fields.Cost" values summed.

If the above is correct, you should be able to achieve it with something like:

db.collection.aggregate([
  {
    '$match': {
      '$or': [
        { 'fields.Revenue': { '$ne': null } },
        { 'fields.Cost': { '$ne': null } }
      ]
    }
  },
  {
    '$group': {
      _id: null,
      TotalRevenue: { '$sum': '$fields.Revenue' },
      TotalCost: { '$sum': '$fields.Cost' }
    }
  },
  {
    '$project': { Profittest: { '$subtract': [ '$TotalRevenue', '$TotalCost' ] } }
  }
])

Which results in (for the provided sample documents):

[ { _id: null, Profittest: 2319.25 } ]

I’ve only tested the above based off the sample documents so you may wish to try this out on a test system with more data to verify theres no odd behaviour. Additionally, it may be easier to add the corresponding fields.Revenue and fields.Cost fields to documents that do not have them (and set the value to 0 as you had mentioned). This will simplify the query although you’d need to change it from the above one if you plan to do so.

I’ve not yet tried this on charts itself as the above tests were done via mongosh but if you still need further help let me know and I will attempt to do this via Charts – If further assistance is required, let me know your expected output on Charts based off the sample documents mentioned here.

Regards,
Jason

[Jose_Olaizola]

Hi again @Jason_Tran, I tried with your code in the query bar on atlas charts and the field has been created but it seems there is no data. the chart stays empty. Here are some reference pictures. Usually I only use compass and atlas charts. For this task I want a bar chart that shows the profit per day. Base on the “Entry Date” Field. Note: Profit field can be negative too. And that should be represented in the chart aswell.

chart empty after applying the query: (this has happened to me in the past with other querys, idk If I’m missing something or any config, I tried with removing and adding the fields again, but that doesn’t work)

[Jose_Olaizola]

Here is the chart empty after applying the query:

Note: This collection have 400k+ documents, idk if that amount may be the issue

[Jason_Tran]

Ah gotcha, are you trying to create a series of column charts to show the profits per month? I had interpreted it as a singular “Overall Total Profit” so my apologies if i’ve misunderstood here.

You might be able to get a similar output to below if you hit the ‘Bin’ icon on the X-axis:

This may not be what you’re after but at least we can see if the data and aggregation worked on your data set

Let me know how this goes.

Regards,
Jason

[Jose_Olaizola]

Hi Again @Jason_Tran , many thanks for your patience and help so far. I removed the Date filter and the X axis, and looks like we got a similar output. so we can say the aggregation is actually working. Now we just need to filter this data by “Date” to have a nice bar chart, I would like to be able to play with the filters, to see profit per day and per month. Per date of the month… last 30 days. You know the usual. here is a pic reference:

note: I’m happy to see some data in the chart finally, I was struggling with that a lot, that is good progress

[Jason_Tran]

@Jose_Olaizola

I would like to be able to play with the filters, to see profit per day and per month. Per date of the month… last 30 days.

I believe you’ll need a different aggregation but also your "Entry Date" field inside the "fields" field appears to be in string format.

If you can try transform the string date to date format for a field, it might help with “per month” / “per day”.

If you’re going for a per month / per day then the aggregation used will probably have to be different too but i’d start with trying to get the date fields first.

[Jose_Olaizola]

@Jason_Tran yes the “Entry Date” field is a string atm. I usually just convert it to date with the “convert type” option in the left bar. Could you help me with the code for that? and I can do fix if needed, like I said before I’m very new with the aggregations

[Jason_Tran]

@Jose_Olaizola

I usually just convert it to date with the “convert type” option in the left bar.

Good catch, forgot about that option

In that case, you might want to try converting the data so that if a field Cost or Revenue doesn’t exist, make it so that it’s value is 0. From here, you could then create calculated field as shown in example below which minuses the revenue and cost:

Calculation expression used for above example is:

{$subtract:['$fields.Revenue','$fields.Cost']}

However, please note that I converted the missing fields to have a value of 0 before making this chart. Example if a document was missing a Cost value, i updated it so that the Cost value was 0 and same for Revenue.

Regards,
Jason

[Jason_Tran]

(Didn’t press “Reply”)

For reference, the updated data I used (It has Revenue and Cost values of 0):

db.collection.find({},{_id:0})
[
  {
    fields: {
      'Entry Date': '2023-11-21T22:24:31.000Z',
      Cost: 0.75,
      Revenue: 0
    }
  },
  {
    fields: {
      'Entry Date': '2023-12-14T01:48:22.000Z',
      Cost: 30,
      Revenue: 1225
    }
  },
  {
    fields: {
      'Entry Date': '2023-11-06T22:40:02.000Z',
      Revenue: 1125,
      Cost: 0
    }
  }
]

For November, we can see that the total profit / loss was = (1125 - 0) + (0 - 0.75) = 1124.25
For December, we can see that the total profit / loss was = (1225 - 30) = 1195

Shown here is by month:

If we do a yearly profit loss = (1125 - 0) + (0 - 0.75) + (1225 - 30) = 2319.25

Shown here is by year:

Regards,
Jason

[Jason_Tran]

Also, with the updated fields, I do not think you will require an aggregation prior to gather /filter the documents or even transform them so this might be easier

[Jose_Olaizola]

@Jason_Tran yes that should be the easiest way, the problem is we have 400k+ documents and like new 5k every day. so what is the simple code to convert those “null” values to “0”, in a safe way?

Maybe we can use $reduce in a pipeline?, I would pref to not edit the data structure and keep everything in the charts

[Jason_Tran]

@Jose_Olaizola

@Jason_Tran yes that should be the easiest way, the problem is we have 400k+ documents and like new 5k every day. so what is the simple code to convert those “null” values to “0”, in a safe way?

Maybe we can use $reduce in a pipeline?, I would pref to not edit the data structure and keep everything in the charts

You can try using $ifNull maybe to see if that works for you in Charts. I created a simple example on the 3 test documents that had appropriate missing fields.Revenue and fields.Cost values but i’d highly recommend testing this out to see if it works for your 400k+ documents on a test namespace.

The pipeline:

[{$set: {
  'fields.Revenue': {$ifNull:['$fields.Revenue', 0]},
  'fields.Cost': {$ifNull:['$fields.Cost', 0]}
}}]

Applied at Charts datasource:

Then using the Calculated field mentioned in my previous reply:

@Jason_Tran

Calculation expression used for above example is:

{$subtract:['$fields.Revenue','$fields.Cost']}

Then converting the date string field to Date data type and placing on the axis’s:

Regards,
Jason

[Jose_Olaizola]

Hi Again @Jason_Tran , that worked like a charm! many many thanks!!! I’m very happy for this.

Now, some bars are under 0$ in the chart, you think is possible to make these negative bars another color, like red for example. here is a reference pic.

[Jason_Tran]

Glad to hear!

You can try conditional formatting to see if this works for negative values but if not or you need further assistance please create a new topic regarding the colour changes.

[tomhollander]

Conditional formatting s only supported for table and number charts, so unfortunately you can’t do this for column charts.

The best way of doing this is to create a calculated field which specifies if the value is positive or negative. Then you can use this in the Series channel and assign a different colour.

[Jose_Olaizola]

Thanks, I will give it a try