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

[Tiger1]

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

[Steve]

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.

[Tiger1]

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.

[Steve]

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?

[Tiger1]

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

[Steve]

Tiger:

This is the column “Document Date” - Always given

None of the columns in your screenshot have names.

[Tiger1]

Sorry:
the table is VERY large:

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

[Steve]

Where will the result be computed and saved?

[Tiger1]

a VC.

[Steve]

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

[Tiger1]

uh oh. Yes i hoped.

[Steve]

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.

[Tiger1]

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

[Tiger1]

Do you have suggestions of changing the model?

[Tiger1]

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