Skip to main content
Solved

Listing the sales amount in different columns based on either U.S. or INT sales

  • 10 September 2024
  • 2 replies
  • 18 views

Forum|alt.badge.img+1

We would like to be able to have two additional columns in a Daily Sales GI. This GI will eventually become a pivot table. One column will have the sales in the US column if the ARInvoice RefNbr starts with an INV, the other will be an INT if the RefNbr starts with INT. My initial thinking for the formula is:

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal])) for the U.S. and =IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal])). Both of these formulas would be in their own columns which I have as U.S. in one caption, and INT in the other caption.

Date          U.S.                 INT             Total

09/01/24    $12,345.00   $460.00      $12,805.00

etc.

The pivot portion shouldn’t be that difficult, just the part separating the U.S. from the INT.

Best answer by lauraj46

Hi @wmatthews1877 ,

You are on the right track.  The IIF function takes three arguments, so your functions would be something like this:

 

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal]), 0)

 

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal]), 0)

 

Give it a try and let us know how it goes.

Laura

View original
Did this topic help you find an answer to your question?

2 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 503 replies
  • Answer
  • September 10, 2024

Hi @wmatthews1877 ,

You are on the right track.  The IIF function takes three arguments, so your functions would be something like this:

 

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal]), 0)

 

=IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal]), 0)

 

Give it a try and let us know how it goes.

Laura


Forum|alt.badge.img+1
  • Author
  • Varsity I
  • 112 replies
  • September 10, 2024

Works like a champ. I just forgot to put the 0 at the end. Thank you.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings