How to pick the value from specific version data without SELECT

CommunityMember126793

Friends need your help !!

I am trying to optimize the model with the help of cross-references so that can remove most of the formulas from the model (multiple modules will be optimized if successful).

 

Source module with 2 months data

April & May months data:

 

Looking for below result in the Target module

Note: No version; hence the intersection of version + month to be selected from source in below like combination

April = 0+12

May = 1+11

June = 2+10 etc….

 

Pattern to be followed for the rest of the modules in the same way

 

Note: So far, may SELECT are placed in the modules line items to pick the specific version data based on month like shown below

 

IF 'SYS01.Time Settings'.MONTH Number = 1 THEN a[SELECT: VERSIONS.'0+12']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 2 THEN a[SELECT: VERSIONS.'1+11']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 3 THEN a[SELECT: VERSIONS.'2+10']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 4 THEN a[SELECT: VERSIONS.'3+9']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 5 THEN a[SELECT: VERSIONS.'4+8']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 6 THEN a[SELECT: VERSIONS.'5+7']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 7 THEN a[SELECT: VERSIONS.'6+6']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 8 THEN a[SELECT: VERSIONS.'7+5']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 9 THEN a[SELECT: VERSIONS.'8+4']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 10 THEN a[SELECT: VERSIONS.'9+3']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 11 THEN a[SELECT: VERSIONS.'10+2']

ELSE IF 'SYS01.Time Settings'.MONTH Number = 12 THEN a[SELECT: VERSIONS.'11+1']

ELSE 0

M.Kierepka

Hi @CommunityMember126793,

 

If you are using Anaplan built-in Versions (which I see you use, because in SELECT clause you have "VERSIONS"), unfortunatelly, there is no simple way to do that - your chain of IFs is probably best you can do. If you would use some list as versions dimension, then you can lookup.

 

You can read more here: https://community.anaplan.com/t5/Best-Practices/To-Version-or-Not-to-Version/ta-p/51410
and here: https://community.anaplan.com/t5/On-Demand-Courses/Native-Version-and-Custom-Version-Lists/ta-p/63728

StevenBeerthuizen

Why not make a system module with months and the related versions and do a lookup on that?

CommunityMember126793

@StevenBeerthuizen  sorry missed to mention in original post. i have a system module also which is linked between month and versions..

 

How to get it linked and pick the specific version data from source?

 

have tried lookup but unsuccessful

Ingilavicus

Hi @CommunityMember126793 ,

 

You don't need to dimensions of both Months/Versiosns. Instead, you should have Months as a dimension and Line Item formatted as versions. Then as @StevenBeerthuizen  already suggested you would be able to lookup the version based on a mapping. 

 

Andris

rob_marshall

@CommunityMember126793 

 

Have you thought about using line item subsets (LISS)?  Take a look at this article:

 

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

 

Obviously, you will not need the variance part, but what you will be able to do is map the Time (month) to a certain LISS member (the line item representing the version) so the lookup's will work.

 

Rob

CommunityMember126793

@M.Kierepka Thanks for your thoughts!

i think i may have to deal with SELECT otherwise it is a design call to replace native version with custom version list.