报告时间表上的变化。
你好. .我有多个甘特表来跟踪几个项目里程碑，以及与这些里程碑相关的客户付款。我的财务团队想要一份月度报告，说明由于项目延误而导致的里程碑付款的变化，以便预测收入。
我的想法是建立一个工作流程，在每个月的最后一天运行。它将把所有已更改的行移动到新工作表中。这将允许我保持这些表的运行总数见下面的例子。有两个不同时间的数据(参见modified列—在本例中以2分钟为单位变化，而不是一个月)。黄色突出显示的是第一个时间点，蓝色突出显示的是第二个时间点，红色完成日期已更改。
我需要报告里程碑付款的延迟。例如，在这个例子中，里程碑2从11/30/22推迟到2/1/23。这需要特别标记。下个月我需要重复这一点。
我尝试使用基线方差函数，它显示了延迟，但它不允许从一个月到另一个月的比较来突出这些变化。我需要能够跨多个表执行此操作，并希望将其滚动到单个报告中。我希望这是有意义的。
最佳答案
-
詹姆斯•马 ✭✭✭✭✭我最近发布了一个执行此功能的样本表。基本上，您正在询问Smartsheet:“查看(1)匹配此PRIMARY列的每条记录，以及(2)具有MODIFIED小于此MODIFIED的每条记录，并给我MAX记录。”然后使用INDEX和MATCH来计算增量。
看一看:
在本例中，最终答案字段中的公式是您想要结束的地方，但不是IF语句，而是计算差值。
如果你用你的数据发布一个表格，并给我链接，我可以为你建立它。
答案
-
詹姆斯•马 ✭✭✭✭✭
我最近发布了一个执行此功能的样本表。基本上，您正在询问Smartsheet:“查看(1)匹配此PRIMARY列的每条记录，以及(2)具有MODIFIED小于此MODIFIED的每条记录，并给我MAX记录。”然后使用INDEX和MATCH来计算增量。
看一看:
在本例中，最终答案字段中的公式是您想要结束的地方，但不是IF语句，而是计算差值。
如果你用你的数据发布一个表格，并给我链接，我可以为你建立它。
-
TCJ ✭✭
谢谢你James，我没有完全按照你表格里的公式来做，但是这个看起来就是我想要的。
下面是我设置的表格的链接:
非常感谢你的帮助。
蒂姆
-
TCJ ✭✭
詹姆斯-我能够把你发表的东西应用到我的表格上-非常感谢你，它工作得很完美!
-
詹姆斯•马 ✭✭✭✭✭
太好了!
帮助文章参考资料欧宝体育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":[]}">
