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.
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.
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.