Collect and Select

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.

Capture001_1474.PNG

Capture002_1474.PNG

Accepted answers

kirsty_ryder

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

All comments

kirsty_ryder

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

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

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?

Quick Links

Can you help?

Unanswered questions

Importing from Data Hub: Incorrect or Missing Mappings in Target Model
When importing data from a Data Hub to another model, I'm seeing values in the target model that don't exist in the source. For example, seller assignments are being tagged with subregions that aren't present in the source data - some assignments are missing expected tags, while others are mapped to seemingly random…
ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.
Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…