Need help to avoid hardcoding SELECT

When creating a model using versions, one of the first questions is, “Should I use Native Versions, or should I create a Custom Versions list?” Will I need to do version comparisons; do the comparisons need to be dynamic? With Native Versions, I can create another Variance version and create a formula, but a) it is not dynamic, b) it creates additional sparsity since Native Versions cannot be subsetted, and c) creating a Variance version goes directly against Planual Rule 1.02-02. Before coming to a decision on which approach to use, you should first read this article— To Version or Not to Version—which speaks to the pros and cons of each method from a performance perspective. Within that article, it correctly points out that you cannot use Versions as a list formatted line item, which leads you to believe you can’t do dynamic version analysis because you can’t use Lookups. This article will demonstrate exactly how dynamic version analysis can be accomplished by utilizing a line item subset (LISS).

Step 1: Create your Native Versions in Version Settings.

Step 2: Create a data module.

This module consists of three lists: Product, Native Versions, and Time and has one line item, Data.

Blueprint view:

Step 3: Create a calculation module.

Since Native Versions cannot be list-formatted line items, we need to create a “dynamic” list of the versions in order for the user to choose which version they want. In order to do this, we must turn the versions into line items. Create a CALC module (CALC01 Versions) with the same dimensionality as the data module excluding versions. Create line items for each version.  Each line item will have a formula pointing to the DATA module (DAT01) using a SELECT statement (Selects are ok to use because Native Versions are structural data).

Step 4: Create a Line Item Subset (LISS).

Go to Settings (the gear), below General Lists, and you will see Line Item Subsets. Create a new one by clicking Insert and then type the name of the Line Item Subset (more information on Line Item Subsets can be found here and here). Please be aware of Planual Rule 1.07-01 which speaks to the naming convention of the LISS. Click OK to save the LISS.

Now that it is named, you will need to associate a module(s) to this LISS. Click the ellipses in the right corner and chose the CAL01 module.

The next step is to choose which line items you want to be associated with this LISS. Click Open and select all “version” line items.

Step 5: Create a Collection module to pull the data in from the Line Item Subset.
The dimensionality should be the same as your DAT module with the only difference being the LISS in place of Versions.

For the formula, type in COLLECT() and make sure you turn your Summaries to None.

As you can see, the LISS now looks and behaves like a regular list, a list that can be used in list-formatted line items.

Step 6: Create a module where the end user will select which versions to render.
Since you will not want users stepping on each other, when line items are used, it is best to dimensionalize them by the User list so the results reflect what the user chose. More information on the User list can be found here. Create two line items, Version 1 and Version 2, both formatted as LISS Versions Transactions.

Step 7: Create a reporting module that will eventually be published to an App or a Dashboard.
Again, the dimensionality should be Time, P2 Product, as well as Users. Create three line items, Version 1, Version 2, and Variance with all formats being Number. The formulas should be as follows:

Version 1: 'COL01 Versions'.Data[LOOKUP: 'INP01 Versions'.'Version 1']
Version 2: 'COL01 Versions'.Data[LOOKUP: 'INP01 Versions'.'Version 2']

Variance: 'Version 1' - 'Version 2'

Since we are using a LISS, we can now use the Lookup function which we couldn’t do using Native Versions.

Now, whenever the Input line items are changed, this module automatically reflects the results.

In summary, these are the items needed:

• Create Native Versions in Version settings.
• Transaction module with all Native Versions defined.
• Create a calculation module bringing in data from the transaction module(s) to line items where the line items are your versions (will need to use the Select function).
• Create a Line Item Subset (LISS) from the above module and line items.
• Create a collection module which “dimensionalizes” the line item versions.
• Create a selection module where the user selects which “version.”
• Create a reporting module that will get the data from the collection module using a lookup.