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

mmoloney
mmoloney
in Modeling

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

Tagged:

Best Answer

  • einas.ibrahim
    einas.ibrahim
    Answer ✓

    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:

    1. Using Formula as a summary method (which you do)
    2. 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.

     

     

Answers

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

     

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

Categories