Previous Contributor

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

9 REPLIES 9
Previous Contributor

RE: How to get the value in Summary level

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?
Previous Contributor

RE: How to get the value in Summary level

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 ""
Previous Contributor

RE: How to get the value in Summary level

[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?
Previous Contributor

RE: How to get the value in Summary level

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.
Occasional Contributor

Re: RE: How to get the value in Summary level

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 "" "

 

 

Highlighted
Community Boss

Re: RE: How to get the value in Summary level

@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

Occasional Contributor

Re: RE: How to get the value in Summary level

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 ""

 

 

Occasional Contributor

Re: RE: How to get the value in Summary level

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?

Community Boss

Re: RE: How to get the value in Summary level

@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