We upload our full SF account list to our Customer Account list at the start of the planning cycle and I wish to delete out accounts which do not have revenue in any versions once the Plan has been completed
I'm created a module dimensionalised by Customer Account & Versions which returns a Boolean if the Customer Account has revenue in that Version but I need to create a single Boolean which determines whether the Customer Account has revenue across any of the versions (without creating a formula which directly references the Versions which I want to avoid because we may add versions in the future & I would then need to manually adjust this formula)
In your example all items except A1 to A3 would be deleted
It's unlikely that we will use the Native Version dimension in the future to do Scenario analysis (this is the situation where there would be revenue across Versions) but it was just a fail-safe so that no Accounts with data was deleted when cleaning up the list.
I'll create an Admin note to adjust the calculation if more than 1 Version is live at the time before the Delete Action is run
May not be the most optimal but perhaps you could create a secondary list (Accounts with Revenue) based on a saved view where the Boolean is true within your module dimensioned by Customer Account & Versions. You will then create a module dimensioned by Customer Account alone, if a matching item exists within the secondary list then set Boolean line item to False. You would then configure a delete by selection action based that line item.
Thanks for the reply but I can't quite see how this would assist in determining whether an account has revenue in ANY version. The 'Accounts with Revenue' list you mention is that Revenue in any Version?
Yes, you would be creating a list of accounts with value in any scenario or conversely without a value if those are the ones you are looking to delete. Mirroring Misbah's example below are some additional details.
Create saved view of Accounts/Versions without revenue i.e. to be deleted.
New List - Accounts to Delete:
Import saved view from above module. By default if each account is referenced more than once (i.e. in multiple scenarios) it will be ignored and only the first occurrence is imported. Assign a default value of 1, this will be used in the next step.
Create Account Deletion Module:
Cross reference Accounts to Delete list to normal list of Customer Accounts. Create delete by selection process.
I guess my query is how to obtain the Boolean results in the 1st Module in your example through a formula which does not directly reference Versions using SELECT etc?
I can create a formula which references the Versions directly but then you have to remember to adjust the formula when a Version has been added before running the Delete Action. I was hoping to be able to skip this step so I could run the Delete Action at the end of the planning cycle without it
@MarkTurkenburg Touche, I guess it doesn't work when you try to go straight the end results (that's what I get for changing my example midstream). So I am back to my original hypothesis. Create a list of Accounts where there is a value then in you accounts to delete module, delete only Customer accounts that do no existing in the Accounts with Revenue list i.e. value = 0. Same concept just slightly different execution.
Creating a line item Any To Delete of the same dimensionality, with formula PREVIOUSVERSION(Any To Delete) OR To Delete, would give you the list of items to delete when you look at the value of the last version. Alternately can do this with NEXTVERSION, and look at value of the first. If you could guarantee that your first or last version didn't change then you could use that.
If you are not currently using 'CurrentVersion' then you could use current version to flag which is first or last - though obviously that has it's own risks (requires updating, could be that a requirement for CurrentVersion is introduced in the future).