N
nekendrick
I'm creating a pivot table listing Accounts Receivable transactions by
customer. It includes both payments and invoices in the results.
Unfortunately both payments and invoices are listed as positive
numbers, so I can't just subtotal the "amount" column.
I've created a formula field in the pivot called "calcamt" that
contains an if statement. The if statement examines the transaction
type code and if it indicates a payment, multiplies the amount column
by -1. Otherwise it just returns the amount column as a positive
number.
When I drag the calcamt field into my pivot, I see the results as I
would expect to, payments as negative amounts and invoices as
positives. However, when I now subtotal this by customer, the subtotal
doesn't reflect the payments as negatives. So my detail shows:
Invoice1: $100
Invoice2: $150
Pmt1: -$100
Total: $350
Any ideas?
customer. It includes both payments and invoices in the results.
Unfortunately both payments and invoices are listed as positive
numbers, so I can't just subtotal the "amount" column.
I've created a formula field in the pivot called "calcamt" that
contains an if statement. The if statement examines the transaction
type code and if it indicates a payment, multiplies the amount column
by -1. Otherwise it just returns the amount column as a positive
number.
When I drag the calcamt field into my pivot, I see the results as I
would expect to, payments as negative amounts and invoices as
positives. However, when I now subtotal this by customer, the subtotal
doesn't reflect the payments as negatives. So my detail shows:
Invoice1: $100
Invoice2: $150
Pmt1: -$100
Total: $350
Any ideas?