How to pick the value from specific version data without SELECT
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
Best Answer
-
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/637282
Answers
-
Why not make a system module with months and the related versions and do a lookup on that?
0 -
@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
0 -
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
0 -
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
0 -
@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.
0
