在Portfolio Rollup中收集多个项目名称?
数据收集在下面的示例摄入表中，指标总结在下面的另一个指标表中。
下面的公式收集项目ID，并给出每个相关平台和状态的计数。然而，我需要在我的投资组合仪表板上显示项目名称以及计数。
如何修复Join公式以收集各种项目名称?(IF语句用于删除零计数，以在散点图中隐藏它们)。
=COUNT(COLLECT({项目ID}，{项目状态}，(电子邮件保护))) =IF(COUNT(COLLECT({项目ID}，{项目平台}，[平台1]$1，{项目状态}，(电子邮件保护)) >= 1, COUNT(COLLECT({项目ID}，{项目平台}，[平台1]$1，{项目状态}(电子邮件保护)))， "") =JOIN((COLLECT({项目名称}，{汇总状态}，(电子邮件保护)))， "， ")
这是我目前得到的输出:
这里是一个示例仪表板，我想在每个平台摘要中显示项目名称。
最佳答案
-
吉纳维芙P。 员工管理你好!谢谢你的标签，@Lucas Rayala
这很奇怪。@breso你介意帮我试几样东西吗?
- 你能把过滤器在源表上使用相同的信息来查看这13行是什么?
- 你能再检查一下吗{项目名称}引用以确保它没有看到另一列，在单元格中不知怎么地只有“项目1”?(或者可能是一张重复的表格?)看到打开的参考窗口的屏幕截图，显示突出显示的列将会很有帮助。
- 如果你加入一个不同的功能:
=加入(截然不同的(收集({项目名称}，{汇总状态}，(电子邮件保护)））， "， ")
最后一个问题……如何在源工作表中填充项目名称列，这是一个手动条目吗?
谢谢!
吉纳维芙
答案
-
breso ✭✭✭感谢您的回复!我复制了公式，但我仍然得到相同的输出“项目一，，，，，，，，，，，，”。
-
breso ✭✭✭
再次感谢!通过项目1，，，，，，，，，，，，，它仍然有相同的输出。在智能表中是否有一个数组函数，也许这些名称正在被空格取代?因为该函数的count版本返回13，并匹配逗号。它应该抓取上表中的所有项目名称，除了那些处于On-Hold (Rollup Status Column)的项目名称。但在这里，如果没有办法做到这一点也没关系，因为我怀疑我能否在仪表板图表中显示所有这些名称。
-
卢卡斯Rayala ✭✭✭✭✭
@breso这很奇怪。试着替换”(电子邮件保护)，加上实际的单词“Active”，看看你得到了什么。@Genevieve P。你遇到过这个吗?简单总结一下，他有一个简单的JOIN—COLLECT函数，该函数应该基于[Rollup Status]从[Project Name]中提取内容。
声明是这样的:
=JOIN(COLLECT({项目名称}，{汇总状态})，(电子邮件保护))， "， "),“(电子邮件保护)对应于汇总状态。当他指着(电子邮件保护)在"Active"这个词前，他得到的是第一个"Project 1"，然后是一串逗号，听不懂。想法吗?
他看到的是这样的:
“项目一，，，，，，，，，，，，”
-
吉纳维芙P。 员工管理
你好!谢谢你的标签，@Lucas Rayala
这很奇怪。@breso你介意帮我试几样东西吗?
- 你能把过滤器在源表上使用相同的信息来查看这13行是什么?
- 你能再检查一下吗{项目名称}引用以确保它没有看到另一列，在单元格中不知怎么地只有“项目1”?(或者可能是一张重复的表格?)看到打开的参考窗口的屏幕截图，显示突出显示的列将会很有帮助。
- 如果你加入一个不同的功能:
=加入(截然不同的(收集({项目名称}，{汇总状态}，(电子邮件保护)））， "， ")
最后一个问题……如何在源工作表中填充项目名称列，这是一个手动条目吗?
谢谢!
吉纳维芙
-
breso ✭✭✭
你好@Genevieve P。＆@Lucas Rayala，
感谢所有的故障排除建议，您的解决方案有效!
- (图1)我在源表(项目输入表)上应用了滤镜。
- (图2)我验证了引用和Rollup Status有正确的来源，但是项目名称链接到项目1的项目元数据表，而不是项目输入表。问题被纠正了!!它找不到项目名称是有道理的。
- 此外，在我纠正项目名称源之前，我测试了DISTINCT函数，它只是删除了所有的逗号“，”。
- 虽然，我现在意识到我不能在散点图中显示名字，也不能制作我想要的项目管道/漏斗/气泡图。
-
卢卡斯Rayala ✭✭✭✭✭
嗨@breso很高兴它起作用了!我知道@Genevieve P。知道该怎么做。
如果你想要一些在视觉上有效的东西，你可以将公式更新为:
=JOIN(COLLECT({项目名称}，{汇总状态})，(电子邮件保护)), CHAR (10))
“CHAR(10)”是一个回车，它将把所有内容作为一个垂直列表放在一个单元格中。如果将鼠标悬停在该单元格上，整个列表将以悬停文本的形式弹出。然而，第一个项目会显示出来，可能会让人感到困惑，他们可能会认为这是唯一的项目。
你可以做两件事中的一件来纠正这个问题:
- 换行文本:这将把单元格的高度扩展到列表的高度。这很可能是不可取的。
- 把公式改成这样:
=IF(COUNT(COLLECT({项目名称}，{汇总状态})，(电子邮件保护)>1， "Hover for list" + CHAR(10) + JOIN(COLLECT({Project Name}， {Rollup Status}，(电子邮件保护))， JOIN(COLLECT({项目名称}，{Rollup状态}，(电子邮件保护)））
这将做什么:如果返回列表多于1项，它将添加短语“Hover for list”作为返回的第一项。这将使人们将鼠标悬停在单元格上触发弹出框并看到其余的内容。只是一个想法!很高兴你至少能解决这个问题:)
帮助文章参考资料欧宝体育app官方888
类别
Try: =SUM([Amount of fees Waived]:[Amount of fees Waived]) + SUM([Amount Written off]:[Amount Written off]) + SUM([Attorney fees]:[Attorney fees])<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106336,"type":"question","name":"True count of Children(), not just non blanks","excerpt":"Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), \"Complete\") = COUNT(CHILDREN()), \"Complete\", \"Not\") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…","categoryID":322,"dateInserted":"2023-06-12T20:08:17+00:00","dateUpdated":"2023-06-12T20:09:57+00:00","dateLastComment":"2023-06-12T22:39:12+00:00","insertUserID":161938,"insertUser":{"userID":161938,"name":"vanZylin","url":"https:\/\/community.smartsheet.com\/profile\/vanZylin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DWO5546FHG2S\/n63YWEB75TVCL.jpg","dateLastActive":"2023-06-12T21:09:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161938,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-13T00:08:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3373211849,"url":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106336,"commentID":380221,"name":"Re: True count of Children(), not just non blanks","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380221#Comment_380221","dateInserted":"2023-06-12T22:39:12+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-13T00:08:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-12T20:39:58+00:00","dateAnswered":"2023-06-12T20:12:44+00:00","acceptedAnswers":[{"commentID":380185,"body":"
Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>
<\/p>
Change<\/p>
COUNT(CHILDREN())<\/p>
to<\/p>
COUNTIFS(CHILDREN(), OR(@cell <> \"\", @cell = \"\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":9975,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
