MongoDB Atlas Charts Calculated Field

Hi, I am completely new to MongoDB and Atlas and I am trying to test out this software to see the extent of its usability.

For a start, I have 4 collections. The first one is the master list of all the things I am selling, the second is the forecast in 2023, the third is the forecast in 2024 and the last is the current inventory level.

Collection 1: Items, Lead time to produce
Collection 2: Items, 2023 Forecast
Collection 3: Items, 2024 Forecast
Collection 4: Items, Current Inventory

I used the master list as the base and used lookup field for the forecast and displayed it as a table on the MongoDB Atlas Charts. I want to highlight the items with the following conditions:

  1. If lead time exceed 1 year, current inventory > 2023 + 2024 forecast, highlight in green
  2. If lead time is less than 1 year, current inventory > 2023, highlight in green.
  3. All others highlight in red.

Are there any way of doing this? I tried creating a calculated field in charts but for some reason, I am not able to write the conditions and when I tried summing 2 columns, it shows as 1 instead of the total sum.

Hi @H_Han - Welcome to the community :wave:

Would you be able to provide some sample documents from each of the collections? This is to better understand the highlight conditions and calculations you’ve mentioned as well.

I’ll need some clarification (which i may possibly get with the sample documents from each collection) regarding the above conditions. For example, “lead time exceed 1 year, current inventory” is not clear for me (is the current inventory collection containing an item id field and boolean field?)

I presume you’re trying to achieve something similar to the following Conditional Formatting - Use Case image but please correct me if i’m wrong here.

Look forward to hearing from you.