How to Translate a Formula like "Cumulative Sum Until.. Sum reaches an Amount"?

1

Good day! Relative Coda newbie here. And I’ve been so excited to use Coda for our company ever since I found out about it!!

Anyway, I’ve been struggling with a new doc I’m creating. I’ve created a sample here for everyone to see:

The gist is I have 3 tables,

  1. Table0 : This is a lookup of items I’ll be referencing in the other 2 tables.

  2. Table1 : This is a daily log of our factory’s produced items.
    –2a) We produce multiple items in the factory.
    –2b) The daily production differs everyday, and is not consistent.
    –2c) We produce our items daily, with or without orders.

  3. Table2 : This is a log of incoming orders.
    –3a) Customers would request their items to be produced fresh. Hence, the Date start column tells us that the customer would like the items to be produced on a date not later than this date.

My question centers around: Table2’s Find Date Completed column.

How do I find the value of this column with the following given constraints: Table2’s Date start column AND Table1’s production log.

So, in the example data in the Sample Doc I shared above: the Find Date Completed column’s value should return 11/3/2020

Looking at Table1’s data, the cumulative QTY Produced of Item A from 11/1/2020 (Date Start) will reach or exceed Customer A’s QTY order (4 units) by 11/3/2020 (1.5+1.2+1.5 = 4.2 units)
But how do I translate this into a formula?

Thanks a bunch in advance!

2

Hi @Steelworld_HEAD,
it took me a while to figure it out…!

I found a solution, although it’s far from being perfect as it needs to overcome with the limitation of the lack of conditional loop (it’s still an open discussion).

I provided the implementation in your document, but basically these are the main componens:

  1. [Production Dates] Find the list of Production Items of that Item starting from the Start Date, and order them by date
[Table1, production].Filter(Item=thisRow.Item && [Date of Prod] >= thisRow.[Date Start])
   .Sort(true, [Table1, production].[Date of Prod])
  1. [Nth Position] Loop to iteratively have incremental sums (this is the tricky one…)
Sequence(1, thisRow.[Production Dates].Count()). // Walk through the Production Items
   FormulaMap( // for each index...
      If(thisRow.[Production Dates].Slice(1,CurrentValue).[QTY Produced].Sum() < thisRow.[QTY Ordered], // if the sum of all items up to the current index is less than the target quantity ...
          0, // print 0 
          1 // otherwise print 1
       )
   ).Find(1) // take the first occurrence of 1
  1. [Find Date Completed] Just select the date from that index
thisRow.[Production Dates].Nth(thisRow.[Nth Position]).[Date of Prod]

I’m sure there might be better approaches, but so far is the only one that comes to my mind

Obviously, if you have a huge amount of data, the computational burden might be relevant.

I hope this helps, anyway: let me know.

2 Likes