How to get the value in Summary level

PrevContributor

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

PrevContributor

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?

PrevContributor

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

derek_hetzler

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

PrevContributor

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.

tobrien

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

 

 

rob_marshall

@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

tobrien

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

 

 

tobrien

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?

DavidSmith

@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