Searching Field value across Time periods

Question

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.

36892362 · Answer

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

Step1Upload/Source the data ( could be 10-15 yrs data) with below key fields
- Report data ( Usually Month end date )        
- Customer Account
- Country
- Currency
- BalanceStep 2Take only last 10 years data from the last month end date data availableStep 3Select 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 5Repeat Steps 4 (a-d) for each quarter end date which has 48 months data by countryStep 6Find 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 7Find the Weighted Average amount between the each month/period vs Attrition % for each country

KSowers10 · Answer

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!

Account Balance.yxmd

36892362 · Answer

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