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