How to get the value in Summary level

Hi, I have two modules: 1. Module01 -> To collect comments from user as a text which published in dashboard:

List Names Line Item
AA Text 01
AA01 Text 02
AA02 Text 03
AA03 Text 04
BB Text 05
BB01 Text 06
BB02 Text 07

2. Module02 ->  To summary and export the content as CSV.

List Parent List Child Line Item 
AA   Text 01
  AA01 Text 02
  AA02 Text 03
  AA03 Text 04
BB   Text 05
  BB01 Text 06
  BB02 Text 07

I am not able to get the values in the summary level (Example in: AA & BB lelvels). I shows empty. since there is a conflict in the content present in the 'List Parent' and 'List Child' i cannot apply formula in summary. is there any way to map the values from module 01 to module 02  at the summary level? Thanks, Goldwin

Tagged:

Answers

  • Goldwin

    My first suggestion would be to see whether you've tried changing the "Summary" property of your Line Item. For text formatted line items, this defaults to 'None'. If you change this to 'Formula', does it return the expected text against the parents?

    If not, please clarify the structure of your modules, i.e:
    - Is 'ListNames' a flat List?
    - Are 'ListParent' and 'ListChild' levels of a hierarchical List or two separate Lists?
    - What is the formula you are using to reference Module01 from Module02?
  • Hi Duncan,

    Anaplan deos not allow to change to formula as 'ListParent' and 'ListChild' hierarchical List. 


    - Is 'ListNames' a flat List?
         Yes this is the flat list
    - Are 'ListParent' and 'ListChild' levels of a hierarchical List or two separate Lists?
       Yes this is levels of hierarchical list.
    - What is the formula you are using to reference Module01 from Module02?
     I am using following formula
       IF COMPARE(NAME(ITEM(List Child)), "AA") = 0 THEN Module01.Line Item(SELECT: Module01.AA) ELSE IF COMPARE(NAME(ITEM(List Child)), "AA01") = 0 THEN Line Item(SELECT: Module01.AA01) ELSE ""
  • [quote=Goldwin Lawrence]Hi,

    I have two modules:

    1. Module01 -> To collect comments from user as a text which published in dashboard:
    List Names Line Item
    AA Text 01
    AA01 Text 02
    AA02 Text 03
    AA03 Text 04
    BB Text 05
    BB01 Text 06
    BB02 Text 07
    2. Module02 ->  To summary and export the content as CSV.
    List Parent List Child Line Item 
    AA   Text 01
      AA01 Text 02
      AA02 Text 03
      AA03 Text 04
    BB   Text 05
      BB01 Text 06
      BB02 Text 07

    I am not able to get the values in the summary level (Example in: AA & BB lelvels). I shows empty. since there is a conflict in the content present in the 'List Parent' and 'List Child' i cannot apply formula in summary. is there any way to map the values from module 01 to module 02  at the summary level?

    Thanks,
    Goldwin[/quote]Hi Goldwin,

    Could you please clarify exactly what error you're experiencing?
  • Hi Goldwin,

    This is a tricky one. Summary Method of 'Formula' in Non-Leaf elements of hierarchy lists don't like following through on aggregation functions or LOOKUP or even x[TEXTLIST: y].

    The only way I have been able to do this to do the following:

    I'm going to suppose that ListChild is the actual list containing the hierarchy and ListParent is simply a virtual list to help describe the problem.

    Also, every element of ListNames is an element of ListChild be it leaf or non-leaf.

    Define Module: FlatText

    Applies To: 'ListNames'

    Define Line Item: Text

    Data Type: Text
    Comment: Free text user input.


    Define Module: Square

    Applies To: 'ListNames', 'ListChild'

    Define Line Item: Text

    Data Type: Text
    Summary Method: Formula
    Formula: IF NAME(ITEM(ListChild)) = NAME(ITEM(ListNames)) THEN FlatText.Text ELSE ""


    Define Module: TreeText

    Applies To: 'ListChild'


    Define Line Item: Text
    Data Type: Text
    Formula: TEXTLIST(Square.Text, "", ListNames, ALL)
    Summary Method: Formula
    Comment: This is the only non-aggregate function I can find that will work.


    The size of the module 'Square' will be the length of 'ListNames' squared so be sure that this will not dominate the size of the model.
  • Thank you for this information. I am running into an issue. The summary is repeating the explanations when it's aggregated to the parents, and the parents without explanations are aggregating those explanations as well. I have a screenshot of my issue, followed by what I thought I'd see and the hierarchy of the accounts (level A3 is lowest). Here are my formulas:

     

    Module = "Define Module: Tree Text"

    Line Item = "Text"  Format = "Text"  Summary = "Formula, Time: Formula"

    Formula = "TEXTLIST('Define Module: Square'.Text, "", 'A3 P&L Object Sub Account', UNIQUE)"

     

     

    Module = "Define Module: Square"

    Line Item = "Text" Format = "Text" Summary = "None"

    Formula = "IF 'P&L: Master Details'.Variance Explanation = "" THEN "" ELSE IF NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) = NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) THEN 'P&L: Master Details'.Variance Explanation & " | " ELSE "" "

     

     

  • @tobrien .

     

    Let me ask you something, what are you trying to accomplish (what is the requirement) as doing what you are doing is not good.  First, please try to not use TextList() as it is a serious performance impact and there are likely other ways of accomplishing what you needing to do without TextList().  Secondly, can you please explain this part of the formula:

     

    IF NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) = NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) THEN 'P&L: Master Details'.Variance Explanation & " | " ELSE "" "

     

    When would the first part not equal the second part?  Shouldn't the formula actual be:

     

    IF 'P&L: Master Details'.Variance Explanation = "" THEN "" ELSE 'P&L: Master Details'.Variance Explanation & " | "

     

    Again, if you can describe what the actual requirement is, it is very possible we can give you a better way as doing string concatenations this way is not optimal.

     

    Thanks

     

    Rob

  • Rob,

     

    Thank you for looking into my issue. I want to rollup comments for variance explanations for the intersections of Accounts and Business Units. PDF example is attached.

     

    As far as the forumla you referrenced, the reason I did that becuase I was trying to avoid the " | " showing up when no comments are present. I tried the formula below but it didn't work:

    IF NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) = "" & NAME(ITEM('sBU - Actual/Budget')) THEN 'P&L: Master Details'.Variance Explanation & " | " ELSE ""

     

     

  • I think I know the issue. 

    This formula:

    TEXTLIST('Define Module: Square'.Text, "", 'A3 P&L Object Sub Account', UNIQUE)

    Should be:

    TEXTLIST('Define Module: Square'.Text, "", 'A3 P&L Object Sub Account' & 'O4 Business Unit' , UNIQUE)

     

    Is it possible to combine multiple lists in the list section of the testlist?

  • @tobrien 

    A couple of Best Practices:

     

    You should also look to split up the formulae

     

    NAME(ITEM('sOSA - Actual/Budget')) & NAME(ITEM('sBU - Actual/Budget')) is repeated twice in the formula, so should be held outside of the formula in a separate line item

     

    Also the join: 'A3 P&L Object Sub Account' & 'O4 Business Unit', should be outside of the TEXTLIST formula too for optimal performance

     

    Also, be careful with TEXTLIST, as it is a very heavy calculation in terms of memory and performance.  In this case, I'm not sure there is an alternative, but exercise caution!

     

    I hope this helps

    David