Need help creating Formula to select Invoice Number when it Contains an "A"

jtrollinger

I need help writing a Formula for an Aggregated Field in a Group by that will select the Latest Invoice that has an "A" in it if it exists. This example only has 1 Credit and Adjustment (C & A Invoice but I could have multiple to I need to take into consideration the Last Invoice Date as well)

Below is my attempt but I keep getting the First Invoice Number created.

MichelleH

@jtrollinger Assuming you are defining the last invoice as the record with the most recent InvoiceDate, then you will need two steps to accomplish this:

1. In the same Group by tile, include this formula to identify the most recent date having an invoice with "A" in the number: MAX(case when `InvoiceNo` like '%A%' then `InvoiceDate` end)
2. Create a separate branch of the dataflow before the Group by that filters to just rows with an Invoice Number containing "A" (`InvoiceNo` like '%A%'). Then join this back to the original branch after the group by on same fields you used in the grouping.

DavidChurchman

Does it work if you take out the "ELSE" clause? Right now, it points InvoiceNo no matter what (when it contains 'A', else when it does not contain 'A').

MichelleH

@jtrollinger Assuming you are defining the last invoice as the record with the most recent InvoiceDate, then you will need two steps to accomplish this:

1. In the same Group by tile, include this formula to identify the most recent date having an invoice with "A" in the number: MAX(case when `InvoiceNo` like '%A%' then `InvoiceDate` end)
2. Create a separate branch of the dataflow before the Group by that filters to just rows with an Invoice Number containing "A" (`InvoiceNo` like '%A%'). Then join this back to the original branch after the group by on same fields you used in the grouping.

DavidChurchman

Does it work if you take out the "ELSE" clause? Right now, it points InvoiceNo no matter what (when it contains 'A', else when it does not contain 'A').