Alteryx data filtration question

[noopurjain]

Hello,

I have been trying to create an audit on Alteryx where I have following requirements:

1) Need all the accounts where "Amount" is 0 for minimum 5 months.

2)  And the latest bill date should not be later than November 2021

3) Please see attached data for reference. Need yellow highlighted fields as output 

[ChrisTX]

Questions:

For "minimum 5 months", do those months have to be consecutive, or could they be any 5 months for the same account?

For "latest bill date should not be later than November 2021", the Excel file you provided indicates the first row is "want this row in output", but the Bill Date for that row is December 2021.  This date is "later than November 2021".

Chris

[noopurjain]

Hello Chris,

Thanks for you reply!

Those 5 months should be consecutive. 

Apologies, I made mistake over there. If the latest "bill date" is after November 2021, it is OK. For example, November 2021, December 2021, Jan 2022 etc. is fine 

Also check row 26 to 32 in the data I have provided. The account 546464 is also billing 0 for more than 5 consecutive months but I don't want it as the last bill date is before November 2021

[Luke_C]

Hi @noopurjain 

Here's a stab at your logic:

1. Create a date field based on 'Bill Date'
2. Sort data chronologically
3. Add a consecutive 0 check (checking both that values are 0 and that the current record is the subsequent month)
4. Get the max consecutive days for each account, filter to accounts with 5+, then join back to limit the data to just these accounts
5. Select the last record (latest date due to sorting) for each account
6. Filter out dates before Nov 2021

Notes:

1. You have account 45678 highlighted in your file, however it doesn't meet your criteria of 5 consecutive months. It has April-July with 0, then Jumps to November with 0. Let me know if I'm missing something:
2. Some dates are '202100'. It is not clear how these should be interpreted.