Multi row formula issue (Possible Macro?)

[Gabocorreia95]

Hi All 

I got a dataset with several accounts which are assigned an exposure ID, the exposure ID is share by multiple accounts, my end goal is to determine how many accounts were already open by the time the account in questioned opened 

My available fields are 

Exposure Group ID 

Account Number 

Opened 

closeddate

I used a multi row formula and input the following expression

If [Exposure Group ID] = [Row-1:Exposure Group IdD]

Then

(If [Opened date] > [Row-1:Opened Date] and [Opened Date] < [Row-1:Closeddate] Then [Row-1]:Accounts opened] + 1 Else 0 endif)

    Else 0 endif

This identifying the accounts opened at the time based on the column right above but is not taking in consideration previous closed accounts I.e.

(All of these accounts are on exposure group 0001)

85001 opened on 01/01/2020 and closed on 01/05/2020

85002 Opened on 01/06/2020 and closed on 01/06/2022

85003 opened on 01/02/2021 and closed on 01/05/2022

85004 opened on 01/07/2021 and closed on 01/06/2023

85005 opened on 01/09/2022 and no close date

My current output shows

85001: 0 this is correct as there were no previous opened accounts

85002: 0 this is correct as by the time this opened 85001 was a;ready closed

85003: 1 correct as 85002 was open when this one opened

85004: 2 CORRECT as when this opened 85003 85002 were open

85005: 3 INCORRECT as by the time this opened 85003 and 85002 we already closed but 85004 was opened so the correct output should be 1

Is there away for Alteryx to check all previous account within the same exposure group? 

Many thanks in advance for your help 

[Brankl]

Hi Gabocorreia95. It looks like your formula checks the previous row and either increments the counter or keeps the counter the same. There's currently no checking if the open date is after the close date for rows that go farther back than 1 previous. You're using Row-1 to refer to the previous row in your formula, but you'd be better off checking your conditions against all rows in the group.

Here's your pseudocode: Sum (if exposure group ID is equal to this exposure group ID AND open date is less than this open date AND close date is not less than this open date).

What's the best way to do this in Alteryx? A multi-row formula is hard, because you may need to go back an indefinite number of rows to check all the conditions. You could use a macro to append a single row's exposure group ID, open date, and close date as new fields a data frame before using the logic to determine the result for that row. Then the macro can iterate through rows and save your answers.

If you're not afraid of the R tool, there are much simpler solutions using a little programming.

result = c()

For (i in 1:length(data_frame)) {

    result[i] = sum(data_frame$ID == data_frame$ID[i] & data_frame$open < data_frame$open[i] & ! (data_frame$closed < data_frame$open[i])

}

[Brankl]

If you end up going the iterative macro route, the Select Records tool is going to be your best friend. You can use the engine iteration number within Select Records to process each row in turn.

[Gabocorreia95]

Hi Brankl 

Thanks for you reply, I've been trying different solutions including the iterative macro route without luck. Could you please explain in more detail the R solution i might want to try that one!

Many thnks 

[AndrewDMerrill]

As long as your data set is not huge. Here is a fairly simple workflow that should do what you're looking for:

[Gabocorreia95]

Hi Andrew! 

Thank you very much for your reply, this is working great except for when we have more than one account opened in the same date as all those accounts should have the same number of pre existing accounts. We shouldn't  include the accounts as pre existing where the opened date is the same 

As an example if three accounts opened in the same date and no previous accounts were opened  the previous account column should show 0 for all of them and then if another account was opened in the following day the previous account column  for this particular row would show 3 

Currently the output using the logic you shared would show 0,1,2,3 for those four rows, rather than 0,0,0,3.

Again thank you for your help 

[Hammad_Rashid]

It looks like you're trying to create a calculated field in Alteryx to determine the number of accounts that were already open at the time each account in your dataset opened, considering both opened and closed dates. The issue seems to be that your current approach is not correctly accounting for closed accounts.

To achieve this, you can use the Multi-Row Formula tool in Alteryx, but you need to make sure you consider both opened and closed dates for each comparison.

Here's an adjusted expression: 

If [Exposure Group ID] = [Row-1:Exposure Group ID] Then
(If [Opened Date] > [Row-1:Opened Date] and ([Row-1:Closed Date] is null or [Opened Date] < [Row-1:Closed Date]) Then
[Row-1:Accounts opened] + 1
Else
[Row-1:Accounts opened]
endif)
Else
0
endif

This expression checks if the current row's exposure group is the same as the previous row's exposure group. If yes, it then checks if the opened date of the current row is greater than the opened date of the previous row and less than the closed date of the previous row (or the previous account is still open). If this condition is met, it increments the count of opened accounts; otherwise, it keeps the count unchanged.

Make sure to replace [Closed Date] and [Opened Date] with the actual field names in your dataset.

This should give you the desired output by considering both opened and closed dates when counting the number of accounts opened at the time each account in your dataset opened.

[AndrewDMerrill · Accepted solution]

@Hammad_Rashid, your solution does not account for opened accounts that occur after an account is already closed (which should no longer add to [Accounts opened] for that record).

@Gabocorreia95, here is an modified workflow (very minimal changes) that adds the new requirement of considering the opening date.

[Hammad_Rashid]

@AndrewDMerrill I appreciate your clarification. To address the scenario where accounts opened after an account is closed should not contribute to the count, we can modify the expression as follows:

If [Exposure Group ID] = [Row-1:Exposure Group ID] Then
(If [Opened Date] > [Row-1:Opened Date] and
([Row-1:Closed Date] is null or [Opened Date] < [Row-1:Closed Date]) and
([Closed Date] is null or [Opened Date] < [Closed Date]) Then
[Row-1:Accounts opened] + 1
Else
[Row-1:Accounts opened]
endif)
Else
0
endif

[Gabocorreia95]

Thank you so much both for your help!