Running Sum in a column yields a circular reference error

I am trying to replicate a column from a Google sheet. A column adds its current value to the next record value from another column. Like =G100 + H101, repeated to the bottom…=G101 + H102.
Here is the code that yields the circular reference. The formula resides in the Total_Net_Consume column.

next_record = All.lookupOne(Date=$Date + datetime.timedelta(days=1), sort_by=‘Date’)

result=next_record.Net_Consumed_kWh_ + $Total_Net_Consume

result

Thanks for any tips. I am drawing a blank.
John P

Hi John!

I created a small example here: #4620 - Grist

In the Total Net Consume column, we have the following formula;

prior_day = DATEADD($Date, days=-1)
prior_net_consume = Table1.lookupOne(Date=prior_day).Total_Net_Consume
$Net_Consumed_kWh + prior_net_consume

First, we need to find what the prior date was (so we can find it’s Total Net Consume). To do this, we use the DATEADD() function. Our start_date is the value in the Date column for this row and all we need to do is subtract a single day. We assign this date to our variable, prior_day.

Next, we need to find the Total Net Consume for the prior_day. To do this, we use lookupOne() to find the first record in Table1 where the value in the Date column is equivalent to the date assigned to the variable prior_day. Then, we use dot notation to specifically pull the value from the Total Net Consume column for the record found. We assign this value to the variable prior_net_consume.

Finally, we add the Net Consumed kWh value to the value we assigned to prior_net_consume to get our current day’s Total Net Consume.

Please let me know if you have any follow up questions!

Thanks,
Natalie

Hi Natalie,
Thanks for your reply. I have tried to get this working in my application but still get a CircRefError.
The table is named All. The two column names I have checked.
My current code is below:

prior_day = DATEADD($Date, days=-1)
prior_net_consumed = All.lookupOne(Date=prior_day).Total_Net_Consumed
$Total_Net_Consumed + prior_net_consumed

See attachment


John

Natalie,
I figured it out. Thanks for the code. Works like a charm.
John

1 Like