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

[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