Searching Field value across Time periods

[36892362]

Hi Team, i have data for 10 years with a field call account number.  Want to create a new field where it will identify the accounts with non zero balance on a particular date , and select these accounts if it is available in all the data available for different dates. 

[KSowers]

Can you provide sample data for this by chance? How do you want the date to be provided?

[36892362]

Unable to share the actual data, just provided a sample table how data, fields in green are already in database. Fields in Red colour need to be created using alteryx.

Report Date ( Data Available only for Monthend dates)	Customer Account Number	Country	Currency Code	Account Balance	30/12/2013	31/03/2014	30/06/2014	.....Quarter end date
31/01/2013	A1				Need to Identify accounts with balance on this date. And all the other dates this account has balance marked as Y else Blank	Need to Identify accounts with balance on this date. And all the other dates this account has balance marked as Y else Blank	Need to Identify accounts with balance on this date. And all the other dates this account has balance marked as Y else Blank	Need to Identify accounts with balance on this date. And all the other dates this account has balance marked as Y else Blank	Need to Identify accounts with balance on this date. And all the other dates this account has balance marked as Y else Blank
28/02/2013	B1
31/03/2013	A1
30/04/2013	B1
31/05/2013	C1
30/06/2015	A1
31/07/2016	D1
30/09/2016	B1
Take data for only last 10 years of the available data

[36892362]

Need to Identify accounts with balance on this date ( this is the header date e.g 30/12/2023) . And all the other dates ( Report date in the 1st column)  this account has balance marked as Y else Blank

[36892362]

Need to Identify accounts with balance on this date ( this is the header date e.g 30/12/2013, 31/03/20144) . And all the other dates ( Report date in the 1st column)  this account has balance marked as Y else Blank

[KSowers]

I believe this is what you are looking for! If so, please mark as solution. If not, please let me know how we can edit it to solve your problem!

[36892362]

Thank you for sharing the workflow but still dont provide me the required solution. I tried to summarize the overall workflow that iam trying to build below.  This query is mainly to address the issue in step 4

Step1	Upload/Source the data ( could be 10-15 yrs data) with below key fields - Report data ( Usually Month end date )         - Customer Account - Country - Currency - Balance
Step 2	Take only last 10 years data from the last month end date data available
Step 3	Select the first quarter end date available in the data, identify the accounts available ( e.g. Mar 2018)
Step 4	a) Check the balances for the Accounts identified in Step 3 for the next 4 years ( 48  months data sets)  by country ( i.e  Apr 2018 to Mar 2022 ) b) For each month/period do % balance change calculation ( i.e ( April 2018 balance - Mar 2018 balance) / Mar 2018 balance......(Mar 2022 balance-Mar 2018 balance)/Mar 2018 balance  by country for the 48 months/periods c) Identify Cumulative Attrition % for 48 months/periods by country. (i,e for the 1st Month/Period, if the value identified in step b is >0, then 0 else take the same value, for the remaining periods check if the value identified in b in the current period > previous period , take the minimum value & if it is greater than 0 take it as 0 d) Identify Discrete Attrition % for 48 months/periods by country ( i.e  for the 1st Month/Period take the same value as identified in step c, for other periods take the  difference of the current period value vs previous period value identified in step c.
Step 5	Repeat Steps 4 (a-d) for each quarter end date which has 48 months data by country
Step 6	Find the Average Attrition% for each of the 48 months/period by country. Keep a control check - Sum of this Average Attrition by country should be 100%
Step 7	Find the Weighted Average amount between the each month/period vs Attrition % for each country