From Native Versions to Fake Version List?

ChrisAHeathcote
ChrisAHeathcote
in Modeling

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, 

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @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.

Comments

  • 1635834
    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
    ChrisAHeathcote

    @1635834 

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

     

  • rob_marshall
    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
    andrewtye

    Beat me to it!

  • rob_marshall
    rob_marshall

    @ChrisAHeathcote 

     

    How often will the users of the model add versions?

  • ChrisAHeathcote
    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
    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.

     

Categories