I need help with a calculation expression

AppSheet AppSheet Q&A
1

Hello everyone,

I hope you can help me.

I’m trying to make an expression to calculate totals.

In the “PROJECTS” table in a virtual column I want to calculate the total sales of the active project and discount the rectified sales.
In the “SALES” table I have the columns:
[Id] is the sales invoice (can be invoice or rectificative invoice)
[Project] of type Ref
[Rectified invoice] is the Id of the invoice that has been rectified
[Total]

It’s easy to do the sum the total for each project, but I can’t find a way to select the total of the rectified invoices.

Example.JPG
Example.JPG1198×399 80 KB

I appreciate your help.

2

I am not sure of the exact calculation you want. Do you want to sum up the amounts of invoices that do not have corresponding rectified invoices and the amounts of rectified invoices? That is in your sample, for Project 1 you just want to sum up the rectified invoice amounts?

1 Like
3

Hi @JCarlos

You need a virtual column in SALES to get the final total if there is a rectified invoice.

Assuming Rectified Invoice is set with type Ref, source table SALES, you could use:

  1. in SALES: virtual column named “_real_total” with expression:
IF(ISBLANK(REF_ROWS("SALES", "Rectified Invoice")),
[Total],
0)
  1. in PROJECTS: virtual column named “_real_total_sales” with expression:
SUM([Related SALES][_real_total])

For reference:

REF_ROWS() - AppSheet Help

ISBLANK() - AppSheet Help

2 Likes