I am working on the accounts data of a certain financial institution.
AccID- unique Acc ID
Rolled Over from- Contains the AccID if the account is rolled over from an older account.
1. Has been rolled over: A binary field that is true if an account has been rolled over. to another account.
2. Open Accounts: Is true if an account is neither Closed(Withdrawal date IS NULL) nor Rolled Over.
Solution till now:
My aim is to segregate the accounts as Closed, Open and Rolled over. Once segregated, these values need to be used in further calculations.
1. Closed: If 'Withdrawal date' <> NULL - already calculated
2. Rolled Over: The accounts for which AccIDs that are present in the 'Rolled over from' column (anywhere) are 'Rolled over' accounts.
I duplicated my data source and linked the two data sources to visualize which AccIds have been rolled over. However, this does not resolve my requirement.
What I need to do is to create another calculated field called ' Has been rolled over', a binary value that indicates which AccIDs have been rolled over.
Please note that creating something like this in the duplicate data source
if NOT ISNULL([Rolled over from]) THEN 1
) did not work for me, because it ended up indicating the accounts that have been rolled over from another account and not the other way round.
3. Open Accounts: I need to create another column with something like:
if (ISNULL(closed accounts) AND ISNULL(Has been Rolled Over))
but with my calculation, the 'Has been rolled over' is actually calculating if an account is a roll-over account and not if an account has been rolled over.
I am fairly new to Tableau and using Data blending for the first time. For better understanidng, I am attaching the data source file that has a 'result required' tab.
The two fields I need to calculate are highlighted in yellow.
Will greatly appreciate any pointers in this regard.