I have a set of data that contains vendor invoice information which needs to be filtered based specific vendors and then on the invoice's notes. Specially, Vendor A and B each provide 1 invoice per week and the notes for those invoices include the dates services were provided that week.
In the first two months of each calendar quarter, I need to have 4 weekly invoices from each vendor before I can close the financials. In the third month of the quarter, I need 5 weekly invoices from each.
I am attempting to do three things.
- I want to be able to upload the full transaction detail into Alteryx and create a workflow that will generate an output that summarizes the invoices for each vendor separate from all the other transaction data.
- If I have not received an invoice that I am expecting I want to be able to use the average of the recent data to estimate the cost of the missing invoice(s) and have that included in the summary in part 1.
- I also want the workflow to be able to determine whether I should be expecting 4 or 5 invoices in the current month.
So far, I have been able to put together a workflow that accomplishes parts 1 and 3 using filters based on the notes and vendor names (attached image) that are then joined and summarized in an excel output table through the render tool.
I cannot figure out how to accomplish part 2 and this is the most crucial part. My issue right now is that if the invoice has not been received the filter obviously recognizes that so the output is all null information. I tried using the Formula and Multi-Row Formula tools to create formula stating that if the fields are null then enter hardcoded information but that isn't working either. Any ideas here?
