Collect and Select

PrevContributor
PrevContributor
in Modeling

Hi all, I have a question concerning the combination of the formula Collect() and SELECT[], let me first explain my setup: - Attachment 1: I have a module 3 collecting various information from several modules for every row item (code2: Mortgage loan fix, Mortgage loan variable, ...), now this module works perfectly and all information is gathered here. - Attachment 2: I made a module 4, that I want to use as a report. So I created for the lines item of module 3 , a subline item list, used as column headers in module 4 . Now I want to create my own line items as rows, to set up a nice report. However, I'm not able to gather the data from module 3. If I use the formula Collect(), it will gather the total from module 3, of course. So I would like to use a formula that collects the data from module 3, selecting based on code2 (Mortgage loan fix, Mortgage loan variable, ...) Is there a certain formula that would work for this situation? - Something like : 'LTP6 Aggregate 3'.collect()[SELECT: code2.'Mortgage loan fix'] , but this formula of course does not work. Thank you, hopefully I made my issue clear enough.

Tagged:

Best Answer

  • kirsty_ryder
    kirsty_ryder
    Answer ✓
    Hi Mickel

    I think this is similar to something I have done recently.

    1. Create a list which mirrors the line items in the calculation model (your module 3). Lets call this "LIST - Module 3 Line Items" (This will be the same list you have used in the module 3 - you don't need to replicate it)
    2. Create a line item in module 4 to hold the data. This could be called "Data" and will have the the formula COLLECT(). This line item will have your line item subset and "LIST - Module 3 Line Items" as dimensions.
    3. The line items in your reporting module can now have a formula such as: Data[SELECT: LIST - Module 3 Line Items.'Mortgage Loan Fix']
    4. Hide the Data line item in your default view and save.

    Let me know if that helps.

    Regards
    Kirsty

Answers

  • PrevContributor
    PrevContributor
    Thank you, creating a module that collects the data first, work perfectly.

    Only I lose the % format by using line item subsets, but it's not a big issue, if I put the decimals on 2.
  • kirsty_ryder
    kirsty_ryder
    Ah yes, this is something I have come up against too. I would be interested to see if anyone can suggest a solution for that but I believe it to be a limitation of the system and have put it forward as an enhancement request.
  • smarzo
    smarzo
    Hi Kristy - What is the list of line items ("LIST - Module 3 Line Items" ) and how is this different from a line item subset? Is this literally just regular list of all the line item names?

Categories