Line Item Subsets and COLLECT() function with calculated % Values

Hi Everyone,

I have a reporting requirement which I cannot seem to perform due to limitations on Line Item Subsets and the COLLECT() function.

I have a Line Item from Source Module A which calculates the % value of one line vs another Line Item (% calc, summary is set to formula) in the same Module. I've created a Reporting Module B which contains a Line Item Subset which includes the '% calc' Line Item Subset Item in order to do some variance analysis.

When I COLLECT() the data from the Source Module A the '% calc' Line Item Subset Item in the Reporting Module B brings in an aggregated figure and not the calculated %.

Is there away to perform this reporting functionality using the Subset Line Item within the Reporting Module B without having to create a separate Module to handle the calculated % for the '% calc' Line Item as my Customer wants to export the Reporting Module B and to have to export two Modules each time would not be feasible.

Thanks in advance for any help.

Kind Regards,

Michael

Reporting_Module_B_-_Calculated_Line_Item_72087.png

Source_Module_-_Calculated_Line_Item_72087.png

Accepted answers

einas.ibrahim

Good morning @mmoloney

I worked on a project that relied extensively on LiSs, sadly I don't have access to that model anymore so I can't review the formulas.
I had all sorts of issues with LiSs including %s - yes there were aggregating over time and list. I was also using QuarterToDate() and YearToDate(); functions that aggregate too so I had to resolve the situation on multiple levels. There were a lot of Ratios and KPIs in the model.

I believe the way I ended up resolving this particular issue was by:

Using Formula as a summary method (which you do)
Brining in the numerator and denominator to the target module (using collect()) then create the % line item in the target module.

If I recall other solutions or workarounds I'll let you know.

I hope the above works or gets you closer to where you want to be.

All comments

Misbah

@mmoloney

As long as dimensionality matches exactly between the two modules numbers should flow as it. However You might not be able to change the format of percentage line items

Here is the Bible for LIS: Seven diff use cases are explained here - see if it helps

Line Item Subsets Demystified - Anaplan Community

mmoloney

Hi Misbah,

Thanks for the reply.

From what I see the formula Line Item just brings in the aggregated amount when the COLLECT() function is used. So when you roll up to the Total Company level, for example, the data displayed for the formula Line Item is the aggregated values of the bottom level calculated value.

einas.ibrahim

Using Formula as a summary method (which you do)
Brining in the numerator and denominator to the target module (using collect()) then create the % line item in the target module.

If I recall other solutions or workarounds I'll let you know.

I hope the above works or gets you closer to where you want to be.

mmoloney

Hi @einas.ibrahim

This is the approach I ended up using. Its not 100% what I was looking for but it's a workable solution for me.

Thanks for taking the the time to reply 😊

Kind Regard,

Michael

einas.ibrahim

Hi @mmoloney

I'm glad it worked for you.
I have to do a lot of workarounds because the bedrock of the model I was working on was a big LiSs. I wasn't happy about all of them. That's why I have a love/hate relationship with LiSs.

Let me know if you run into other LiSs issues, maybe I faced it too and I can help.

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…