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  

Answers

  • 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.
  • Peter,

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

    Bruce