Need help to avoid hardcoding SELECT

sagarkpr
sagarkpr
in Modeling

Hello,

Need some guidance on how I can avoid hardcoding.

Let say I have a module A that applies to Country and Product (Coke and Pepsi) and line item as sales

I have another module B that applies to country and I want to create two line items Sales - Coke and Sales - Pepsi which I then want to compare variance.

This is the formula I am currently using

Sales - Coke = A.Sales[Select: Product.Coke]

Sales - Pepsi = A.Sales[Select: Product.Pepsi]

How can I avoid the hardcoding for SELECT? I tried creating a constant module and creating two lines item type Product. Using them in the SELECT like below gives me error

Sales - Coke - A.Sales[SELECT: Constants.Product1]

The name Constant.Product1 used in the SELECT clause is not recognized as a list member or version in the model

Any suggestions?

Tagged:

Best Answers

  • rob_marshall
    rob_marshall
    Answer ✓

    @sagarkpr

    Instead of using SELECT, try using a lookup.

    Sales - Coke - A.Sales[LOOKUP: Constants.Product1]

    With that said, if you are going to hardcode both Coke and Pepsi as line items, why not have that list in defined in the module so your only line item is Sales?

  • Vamsi_Kaki
    Vamsi_Kaki
    edited October 2023 Answer ✓

    Hello @sagarkpr , If you would like to provide users to compare sales of selected products then,

    1. Create a filter module (FL1) dimensioned by Users and Line items(Product1, Product2). These line items are product list formatted.
    2. Then create Target module (TR1) dimensioned by Country, Users and Line items(Product1, Product2)
    3. Now, reference sales from source module (dimensioned by Country n Products) by looking up Product1, Product2 line items from Filter module.

    Source Module:

    image.png

    Filter Module:

    image.png

    Target Module:

    image.png

    Thanks,

    Vamsi

Answers

  • sagarkpr
    sagarkpr

    Thanks for the suggestion for Lookup, it worked. When I tried it before, it was giving me error. Must be typo.

    As for not using Product as dimension in Module B. There are about 10 products and at any given time, I would like to compare two products.

  • rob_marshall
    rob_marshall

    @sagarkpr

    Then if you are comparing the two products and those two products can be dynamic (user selects it), then create a module (dimensionalized by Users - which could be expensive depending on the number of users defined in the model), with two line items: Product A and Product B (theses should be list formatted)…The you can use these for your lookup. Also, take a look at this document, it should help. While it is based on Native Versions, it can still by applied to your situation

    https://community.anaplan.com/discussion/98336/variance-analysis-with-native-versions-made-easy

Categories