User selected version comparison

bruce_cavella

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  

peter_mcanena

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.

bruce_cavella

Peter,

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

Bruce