GOOD MORNING. I’ve already asked this question in another section and now I’m asking the developers directly. I have a “Bilancio” table which groups together a series of values from the “Consuntivo” table. I have a certain number of values (in pink in the “Bilancio” table) that derive from calculations of rows of the same table, as indicated by the “Op” column. I can’t find a practical method to do these operations and obtain a final result as listed in the “bilancio” table. In practice it is a normal income statement of a company where we have a list of costs with intermediate values calculated. Do you have a practical idea how to do this within grist? I hypothesized to get out of grist by connecting a google sheet with n8n to do the calculations and then put it back in grist…
Hi there!
I created an example for you here: Community #3133 - Grist
I made an assumption that you have a column in BILANCIO that contains the sum for each group in TIPO COS_RIC.
In Grist, each record has a unique ID that is assigned upon record creation. You can reveal this ID by adding a formula column with the formula $id. If you have deleted any records or have re-arranged columns or applied any sorts/filters, these IDs won’t be in order. We can use a helper table to make a list of IDs in the order they appear in the table - this will allow us to do our mathematical operations in the correct order. Just an FYI, it’s not necessary to add ID as a column but I added in the example just to show what is happening in the first step with the helper table.
The formula used in the Cumulative IDs column of the Helper Table is:
BILANCIO.lookupRecords(sort_by='manualSort').id
You’ll see that the IDs are listed in the same order that the appear in the table.
Now, in the BILANCIO table, I have a column, Value, that shows the sum value for each group listed in the TIP COS_RIC column.
The formula in the Value column is:
SUM(CONSUNTIVO.lookupRecords(Riclassifica=$TIPO_COS_RIC).Saldo)
This formula uses lookupRecords() to lookup all records in the CONSUNTIVO table where the value in the Riclassifica column matches the value in the TIPO COS_RIC column in this table. For each value where a match is found, the value in the Saldo column is returned.
Then, we use SUM() to sum all of the values from the Saldo column for the records found above.
The column TOTALS uses this value along with the operation value in the Op column to find our totals.
The formula used in the TOTALS column is:
order = Helper_Table.lookupOne().Cumulative_IDs
index = order.index($id)
previousId = order[index-1] if index > 0 else 0
previous = BILANCIO.lookupOne(id=previousId)
if previousId == 0:
if $Op == "+":
return $Value
if $Op == "-":
return -$Value
if $Op == "+":
return previous.TOTALS + $Value
if $Op == "-":
return previous.TOTALS - $Value
if $Op == "=":
return previous.TOTALS
We’ll break this down line by line.
order = Helper_Table.lookupOne().Cumulative_IDs : We use lookupOne() to find the first record in the Helper Table then we pull the value from the Cumulative IDs column for this record. Because there is only one record in the table, using lookupOne will find our one and only record. We assign our Cumulative IDs value to the variable order as this is the order in which our records appear in the table.
index = order.index($id) : We use Python’s Index() which takes the record’s ID and returns the index of that ID in the list order. We assign this to the variable index.
previousId = order[index-1] if index > 0 else 0 : In the last step, we found the index of the current record’s ID. In order to figure out the previous ID, we find the ID listed before the current one in order. If there is no ID listed prior, meaning our current record is the first record in the list, then the formula returns 0. We assign this ID value to the variable previousId.
previous = BILANCIO.lookupOne(id=previousId) : We use lookupOne() to find the record in the BILANCIO table where the record ID matched the ID we found in the formula assigned to the variable previousId. We assign this record to the variable previous.
Now, we use this information in our if statements. if statements work top to bottom. If the statement is True then the value is returned. If it is False then the formula moves on to the next statement.
if previousId == 0:
if $Op == "+":
return $Value
if $Op == "-":
return -$Value
When we found previousId, if there was not a value in the list prior to this record’s ID, then we assumed this ID was first in the list and the formula returned 0. Based on the value in the Op column, we return either the positive or negative value from the Value column.
if $Op == "+":
return previous.TOTALS + $Value
If the value in the Op column is + then the formula returns the value from the TOTALS column from the previous record and then adds the value from the Value column.
if $Op == "-":
return previous.TOTALS - $Value
If the value in the Op column is - then the formula returns the value from the TOTALS column from the previous record and then subtracts the value from the Value column.
if $Op == "=":
return previous.TOTALS
If the value in the Op column is = then the formula returns the value from the TOTALS column from the previous record with no change as that is our final total.
I modified the column formatting so only text in the columns where Op is = shows. If you wish to do this, you can change the default cell style to white text on white background. Then, add conditional formatting with the conditional formula $Op == "=" and set the cell style to black text on white background.
Please let me know if you have any follow up questions.
Thanks,
Natalie
1 Like
THANK YOU . I never would have gotten there by reading the python documentation… too advanced for me. However, I have an additional difficulty. in the “Summary” table, the data is actually obtained and listed by month. The forecast always and exclusively refers to the last month. I can filter it in the “final balance” table but the lookuprecords formula always sums the data of all the months (in my case of years). Obviously the data is useless in this case. How can I handle the result based on the date filter? Finally, I am left with the problem of data exposure. Don’t you think that at this point even based on my limited python knowledge it’s better to look into exporting the grist data in csv format or using n8n to link to google sheets and then looker (data studio) for exposure of the 'information (I have to work on it but perhaps you can already tell me if I can re-import the data processed and displayed with looker in grist).
So sorry for the delay in responding to your question! I was out for a week and missed the notification.
I modified the example here to include Date: Community #3133 - Grist
First, I added a Date column to Consuntivo. Then, added a formula column, Month, with the following formula;
$Date.strftime("%B %Y")
This uses Python’s strftime() method to pull the Month and Year component from the Date column. We’ll use this to sum all appropriate records in the Bilancio table.
You can hide the Month column from your view if you’d like.
In the Bilancio table, I have also added a Date column - here, you can select any day of the month. For ease of entry, I selected the first of the month. Then, I added a formula column, Month, with the same column we saw earlier;
$Date.strftime("%B %Y")
Again, we are using Python’s strftime() method to pull the Month and Year value for the Date listed.
Next, we need to update the formula in the Value column to the following;
SUM(CONSUNTIVO.lookupRecords(Riclassifica=$TIPO_COS_RIC, Month=$Month).Saldo)
This is nearly the same formula as we had before but now we add Month=$Month, so now we are looking up all records in the Consuntivo table where the value in the Riclassifica column matches the value in the TIPO COS RIC column of this table and also where the value in the Month column matches the value in the Month column of this table.
For example in Row 2, TIPO COS RICO is Type C and Month is December 2022 so the formula finds all records in Consuntivo where Riclassifica is Type C and Month is December 2022. For all records found, it returns the SUM() of the values in the Saldo column for those records.
Now, you’ll notice that between Row 6 and Row 7, we change from December to January but the TOTALS column continues to add. We need to update the formula in the TOTALS column to the following;
order = Helper_Table.lookupOne().Cumulative_IDs
index = order.index($id)
previousId = order[index-1] if index > 0 else 0
previous = BILANCIO.lookupOne(id=previousId, Month=$Month)
if previousId == 0:
if $Op == "+":
return $Value
if $Op == "-":
return -$Value
if $Op == "+":
return previous.TOTALS + $Value
if $Op == "-":
return previous.TOTALS - $Value
if $Op == "=":
return previous.TOTALS
The only thing we need to update here is in the formula for the variable previous. We need to add Month=$Month to our lookupOne arguments.
Now, TOTALS will calculate each month individually.
I don’t have any experience using Looker Studio but data can be transferred from Looker Studio to Grist as a csv file so if you prefer to use that then send the data to Grist, it can be done!
Thanks,
Natalie
Your help is always greatly appreciated. I understood your solution, but I can’t apply it in my file. As in the image I limit myself to inserting the formula only for the first part, not inserting the month variable, this is because it already doesn’t work like this. I only get a list of “0”. I imagine it is due to the fact that in the “Consuntivo” table the value of the “Riclassifica” cell is related, while in the “Bilancio” table the value of the “TIPO COS RIC” cell is inserted directly. Is this what causes the formula to malfunction or is there another trivial reason that I can’t see??