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
Natalie,
I figured it out. Thanks for the code. Works like a charm.
John
