Replacing null data after using filters with new data

[lbaker]

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.

1. 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.
2. 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.
3. 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?

[aatalai]

Try using the summarise tool before the fitler group by vendor and then average it and then use a join after identifying the missing invoices to marry up the data, does that sound like it might resolve the issue?

[Qiu]

@lbaker 
Replacing null data after using filters with new data, to achieve this, maybe we can use the Time Stamp (DateTimeNow) of running workflow, then filter any data that is smaller than current date, then we can do an average on the past data, after than joining back to the current data to update those Null values?

If you can upload your current workflow, maybe we can take a closer look.

The data you uploaded seems to be incomplete and only containing Periods in Month of 4.