Need help with the correct expression to calculate based on column types

I need help writing the expression to calculate (sum) a row:

I need:

If “Column Type” = “Sales/Invoice” [Current Trx Amount] - [Current Trx Amount] (if the “Column Type” is “Credit Memos”.

Here is what i currently have (It is a TRAIN wreck - I know!!!):

IFS( [Document Type] = “Sales / Invoices”,

[Current Trx Amount] - IFS( [Document Type] = “Credit Memos”, [Current Trx Amount])
)
)

Here is the table (example) showing the raw data i need to calculate:

Thanks

Your expressions look valid to me. Of course, I have no idea what you’re trying to accomplish. Please explain what you’re trying to do in plain language.

Sorry, Yes.

So i need to calculate Sum. The sum should take all “Sales / Invoice” - “Credit Memos” - for that current time (from date column)

So from this example:

I would need:
0-30 Days (Based on Date): $16,000 - $16,000 = $0
Over 90 Days: $943,252

I want to add:
Both “Sales / Invoice” and “Credit Memo” will use the value in “Current Trx Amount” to calculate the expression.

1 Like

What is “that current time”? How is the “current time” determined and/or recognized?

Where did $943,252 come from for over 90 days?

Sales / Invoices should always be additions? Credit memos always subtractions?

What is “that current time”? How is the “current time” determined and/or recognized?
This is the column “Document Date” - Always given

Where did $943,252 come from for over 90 days?
This means $943,252 hasnt been paid since over 90 days ago - determined by the column “Document Date”

Sales / Invoices should always be additions? Credit memos always subtractions?
Yes and Yes

1 Like

None of the columns in your screenshot have names.

Sorry:
the table is VERY large:

I cannot get a snapshot of those rows showing the top columns names

1 Like

Where will the result be computed and saved?

a VC.

In the same table? That’s going to be hugely expensive!

uh oh. Yes i hoped.

I’m afraid I’m unwilling to help with this. Your data model is fundamentally flawed and will result in crippling performance problems over time. I’m not going to contribute to that.

1 Like

oh no. Do you have suggestions of changing the model?

Do you have suggestions of changing the model?

Steve,
What if i do the calculations in the google sheet instead? I would add more columns (0-30 Days, etc…)?