New Contributor

User selected version comparison

Hi, I have a module in which variance calculations are executed on 2 static versions. However, I'd like to set up a variance module in which the user can select two versions from a list of versions to be used in the calculation. I've set up a 'Budget Selector' module with 2 line items (Budget #1 & Budget #2) and have tried multiple configurations - Boolean, text, list formatted - but I can't seem to get the Version name into a SELECT statement to retrieve the proper data. As an example, I've tried this: 'Raw Data'.Amount[SELECT: VERSIONS.Budget Selector.'Budget #1'], but no luck. Any suggestions? Thanks, Bruce  

2 REPLIES 2
Regular Contributor

RE: User selected version comparison

You can't, and you can't use LOOKUP with versions either, which is what you'd do with any other list. All you can do is write it all out in a big IF statement like this:
IF Version Selector = Version Options.'Budget #1'
THEN
    'Raw Data'.Amount[SELECT: VERSIONS.Budget Selector.'Budget #1']
ELSE
    IF Version Selector = Version Options.'Budget #2'
    THEN
        'Raw Data'.Amount[SELECT: VERSIONS.Budget Selector.'Budget #2']
    ELSE
        0
which assumes you've used a list-formatted line item to select your versions, based on a custom list called Version Options, which you'd have to keep in synch with the real version list. Alternatively, if you don't want to hard-code the versions into your formula, you could have a list that let you pick how many versions back you want to look, then code it up like this:
IF Version Selector = Version Options.'Look Back 1 Version'
THEN
    PREVIOUSVERSION('Raw Data'.Amount)
ELSE
    IF Version Selector = Version Options.'Look Back 2 Versions'
    THEN
        PREVIOUSVERSION(PREVIOUSVERSION('Raw Data'.Amount))
    ELSE
       IF Version Selector = Version Options.'Look Back 3 Versions'
       THEN
           PREVIOUSVERSION(PREVIOUSVERSION(PREVIOUSVERSION('Raw Data'.Amount)))
       ELSE
           0
You can add as many PREVIOUSVERSION(PREVIOUSVERSION()) clauses as you like, even if you haven't got that many versions.
New Contributor

RE: User selected version comparison

Peter, Thanks for the help. Solution #1 works, but is is long and will need to be maintained.  Bruce