Skip to main content

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.

Hi @wmatthews1877 ,

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

 

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

 

=IIF(LEFT(LARInvoice.RefNbr],3) = 'INT', (TARInvoice.OrigDocAmt] - tARInvoice.FreightTot] - tARInvoice.TaxTotal]), 0)

 

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

Laura

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

Reply


Terms & ConditionsCookie settings