when we should use select function . can anybody tell me with an example or scenario . 


  • @saurabh_kumar_kunwar 

    SELECT is a very useful where you want to get data from a specific list element and this list is a structural data (not production data).

    Let's say you have 5 plants as your business units in the production chain. This is your structural data and the plants list is non-production.  There is an invertory module dimensioned by plants/products/time. It stores closing stock data. You have very different calculation logic for one of your five plants ("Plant A") and you want to create a module for this plant only. This module obviously don't need to have Plants dimensions. So to collect closing stock from inventory for this plant you will have to choose it. You can do it with the formula looking something like this: Inventory.Closing Stock[SELECT: Plants.Plant A]


    Another very common use case is when you have to take top level data of the list. For example, when you want to get a percentage ratio of a value divided by top level element of this list value. Plant A / All plants, Plant B  / All plants, ets. This may look like Inventory.Closing Stock/Inventory.Closing Stock[SELECT: Plants.All Plants]


    One more case when you need All periods data (when you have more than one year of your time scale). - Value[SELECT: TIME.All periods]

    Or when you have to calculate delta for two versions. Value[SELECT: VERSIONS.FORECAST] - Value[SELECT: VERSIONS.ACTUALS]


    More you can read in Anapedia about SELECT:

  • @saurabh_kumar_kunwar 

    As a general rule use SELECT to 'select' data from a target that either relates to a top level item of a list or a time item such as All Periods, Year to Date, Year to Go or Current Period.


    It can also be used when retrieving data from a specific version.


    A few examples;

    Data[SELECT:TIME.All Periods]


    Data[SELECT:Cost Centres.All Cost Centres] 


    As @KirillKuznetsov  has stated you should only use SELECT on list items that are non Structural data ( in other words will not be updated on a regular basis like product, locations etc ). If using SELECT on these list you can target the Top Level item as this is unlikely to change. 


    If you need to retrieve data from a specific list item then the best approach is to use a LOOKUP function instead.  

  • @ChrisAHeathcoteagree

    Thanks for your added details!