From Native Versions to Fake Version List?

ChrisAHeathcote

Hi All, 

As it is not possible to SUM down native versions is there a simple mechanism to take data out of versions and restate it within a fake version list?

I know this is achievable via a series of nested IF THEN ELSE queries but this approach is not sustainable as it needs to be updated every time a new native version item is added to the model.

All the best, 

ChrisAHeathcote

@rob_marshall 

The link doesnt quite give me what I am looking for. 

While using a LISS works to get data out of native versions and into a list it still involves significant manual intervention if the model owners add more versions. 

 

I have managed to devise a method which uses a boolean mapping of Native Versions and Versions List to stage the data before pulling this data into a module using a versions list.

 

1. Create a new native version - ensure this is set as read only.
2. In version formulas sum all other versions into this new version.
3. Create a systems module dimensioned by native versions and the versions list. Add a boolean line item and check the data points that intersect each similar version in both lists.
4. Create a staging module and use the boolean as the criteria within an IF THEN ELSE function to pull through values from the native version dimensioned source into the hybrid staging module.
5. Use single SELECT function in the target module to select the 'Top Level' version in the staging. 

   Using this approach requires minimal maintenance;

   1. Update the version formula
   2. Update the boolean

Where data has to be moved in and out of native versions and versions list multiple times using a LISS becomes even more cumbersome.

 

Hope this approach helps others too.

1635834

Hi @ChrisAHeathcote 

 

Instead of using IF ELSE, another way to achieve this is by manual mapping i.e., creating a mapping module and map native, custom versions(versions list) then use LOOKUP function to get the data from native to custom versions.

 

Disadvantage: we need to manually map newly created version each and every time.

 

Regards

Shirisha.

ChrisAHeathcote

@1635834 

It is not possible to use LOOKUP as native versions can not be assigned to line item format.

 

rob_marshall

@ChrisAHeathcote 

 

A line item subset (LISS) will be your friend here.  You don't have to follow all of these directions, but you will get the idea...

 

https://community.anaplan.com/t5/How-To/Variance-Analysis-With-Native-Versions-Made-Easy/ta-p/98336

 

Rob

andrewtye

Beat me to it!

rob_marshall

@ChrisAHeathcote 

 

How often will the users of the model add versions?

ChrisAHeathcote

For the use case which prompted this query they add versions every quarter. 

Rather than cycle through existing versions they would rather create a new one and delete any unused versions. 

Therefore, using a LISS or nested IF THEN ELSE requires them to update the formulas/mappings each time. As this transformation occurs multiple times it can be a significantly time consuming and error prone process. 

luke_e

Have been working through a similar scenario (and have also run into the same issues).

 

Sounds like you have an approach that works for you, but have attached my 2c.

 

Haven't found a way I'd say I'm happy with, but given the sizing implications of having to stage modules along the way, I opted to use a SELECT/SUM approach to do the transfer from native version to fake version in one step.

 

My approach was,

1. having the source module by version
2. target module has same lists but instead of version, using fake version
3. using a formula in the target module of source line[SELECT:Version 1, SUM: Mapping Module.Fake Version 1] + source line[SELECT:Version 2, SUM: Mapping Module.Fake Version 2].. etc

With the data now applied to the appropriate fake version,

1. I'm free to either use it as is, or use a LOOKUP to send it elsewhere
2. I can also setup other reports with toggles which allows users to choose which fake version is used in reports/calculations, and
3. I can also subset the fake version list further if only a few of the versions were required. 

My scenario however didn't involve frequent updating of versions and even if we did, we only had ~6 lines to update which would take ~5m. Only other implication is that changes are WSA-only and need to be deployed.

 

kjacokes

Adding my hot take.

I appreciate and am grateful for the alternatives proposed to @ChrisAHeathcote's solution, especially from Rob Marshall. I'm smart enough to know I should listen to those who are wiser and smarter than me, which most certainly includes Rob. ;)

That said, @ChrisAHeathcote, I think your solution is a brilliant solution, and one we've adopted, because it is:

1. Clever, but familiar: Essentially you're just creating a “Top Level” item for native Versions. This is a concept we use all over the place.
2. Solves a universal problem: I want the ability to leverage a custom Versions selector for, well, any report, really. And I want to be able to do it without having to go through a significant number of steps. As long as my Native and Custom Versions lists are in sync, creating a top level essentially gives me the ability to leverage a Custom Versions list for any calculation module, intersect it with Native Versions, and do a LOOKUP on a Custom Versions selector to compare values across Versions.
3. Performant: This being a calculation on a “dense” dimension, it's still quite performant. If we have 10 Versions at any point in time, adding All Versions only increases the size of my dense blocks by 10%. Finding 10% efficiencies elsewhere is probably doable by following PLANS and good practices for peak performance. I'm probably using or applying “dense" incorrectly here, just trying to translate Essbase concepts to Anaplan concepts, perhaps inaccurately, but if I'm wrong I'd love to be englightened!
4. Maintainable: We can, few a through actions, create a process to keep our Native and Custom Versions lists in sync, and in doing so use a TEXTLIST() to form the Versions formula for our “Top Level” Versions item, thus automating this maintenance.

I think there are other merits that make this solution a great one, but I'll stop there. I just wanted to name a few reasons I think it's excellent, and let you know how much I appreciated this creative and helpful write-up.

Cheers,

Kevin