From Native Versions to Fake Version List?

ChrisAHeathcote
Community Boss

From Native Versions to Fake Version List?

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, 

Chris
HeathcoteAndHerran.com
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisAHeathcote
Community Boss

@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.ChrisHeathcote_0-1636379280691.png
  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.ChrisHeathcote_1-1636379386441.png
  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.ChrisHeathcote_2-1636379500545.pngChrisHeathcote_3-1636379529437.png
  5. Use single SELECT function in the target module to select the 'Top Level' version in the staging. ChrisHeathcote_4-1636379605409.png

    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.

Chris
HeathcoteAndHerran.com

View solution in original post

8 REPLIES 8
1635834
Contributor

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
Community Boss

@1635834 

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

 

Chris
HeathcoteAndHerran.com
rob_marshall
Moderator

@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
Master Anaplanner/Community Boss

Beat me to it!

ChrisAHeathcote
Community Boss

@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.ChrisHeathcote_0-1636379280691.png
  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.ChrisHeathcote_1-1636379386441.png
  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.ChrisHeathcote_2-1636379500545.pngChrisHeathcote_3-1636379529437.png
  5. Use single SELECT function in the target module to select the 'Top Level' version in the staging. ChrisHeathcote_4-1636379605409.png

    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.

Chris
HeathcoteAndHerran.com
rob_marshall
Moderator

@ChrisAHeathcote 

 

How often will the users of the model add versions?

ChrisAHeathcote
Community Boss

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. 

Chris
HeathcoteAndHerran.com
luke_e
Contributor

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.