Line Item Subsets and COLLECT() function with calculated % Values
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.
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.1
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 Community1
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.0
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 😊
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.0