指标没有按预期定期更新

克里斯汀·曹
克里斯汀·曹 ✭
在公式和函数

不确定是否有人正在经历这种情况。我为每个项目创建了一个METRICS表，汇总了任务运行状况的计数，以便通过图形在仪表板上显示。我注意到这些指标不会自动更新，除非我刷新页面十几次或重新编写完全相同的公式。但这并不能保证问题会得到解决。我在检查项目计划时碰巧注意到了。奇怪的是，同一张表上的其他度量公式是自动更新的。这是有问题的三个度量数据的公式。是否有一些技巧可以更好地解决或书写问题?所有这些都使用外部参考表，即项目计划。

逾期=条件统计({}1}{水平,< > 1}{水平,< > 2)

=COUNTIFS({项目计划状态}，"已完成"，{Level}， <>1， {Level}， <>2)

=COUNTIFS({项目计划状态}，"未启动"，{Level}， <>1， {Level}， <>2)

image.png


标签:
· 在Facebook上分享 在Twitter上分享

最佳答案

  • 马特•约翰逊
    马特•约翰逊 ✭✭✭✭✭✭
    ✓回答

    嗨@Christine曹

    没有问题。添加一个日期列(可能隐藏它)。然后点击表格左上角的自动化按钮。从那里，从头开始创建新的。我把我的自动化称为与我添加的列相同;“今天(页刷新)”。下面是我如何设置自动化。我希望这能奏效。马特

    image.png


    马特•约翰逊

    Sevan技术

    Smartsheet白金合作伙伴

    · 在Facebook上分享 在Twitter上分享

答案

  • 马特•约翰逊
    马特•约翰逊 ✭✭✭✭✭✭

    嗨@Christine曹

    你听说过TODAY功能需要每天刷新吗?如果是这样，而事实并非如此，那么忽略下面的内容。

    发生这种情况的一个原因是，源表上的列是基于今天的日期自动生成的。举个一般的例子，如果估计的完成日期在过去，或者少于今天，并且状态没有完成，那么标记为“过期”。如果工作表没有打开，或者每天刷新，那么公式可能无法工作，直到它被打开。解决这个问题的方法是添加一个助手列，名为“Auto-TODAY”或“TODAY trigger”，然后将一个自动化与之绑定，这样它每天都会在凌晨1点或其他时间更新今天的日期。这确保了所有的东西每天都焕然一新，不管它是否打开过。

    这可能不是解决办法，但我想我把它扔在那里，以防万一。

    我希望这对你有帮助。

    马特

    马特•约翰逊

    Sevan技术

    Smartsheet白金合作伙伴

    · 在Facebook上分享 在Twitter上分享
  • 克里斯汀·曹
    克里斯汀·曹 ✭

    @Matt约翰逊谢谢你的意见，马特。我不知道这个今天的功能必须刷新。我得再调查一下。TODAY函数包含在公式中，用于计算源表(项目计划)上的“RYG”，这是该度量所计算的内容。所以你的评估可能是正确的。我如何创建这个“助手”，自动化，以便源表总是以今天的日期更新?建议我在哪里可以找到这个指南?

    · 在Facebook上分享 在Twitter上分享
  • 马特•约翰逊
    马特•约翰逊 ✭✭✭✭✭✭
    ✓回答

    嗨@Christine曹

    没有问题。添加一个日期列(可能隐藏它)。然后点击表格左上角的自动化按钮。从那里，从头开始创建新的。我把我的自动化称为与我添加的列相同;“今天(页刷新)”。下面是我如何设置自动化。我希望这能奏效。马特

    image.png


    马特•约翰逊

    Sevan技术

    Smartsheet白金合作伙伴

    · 在Facebook上分享 在Twitter上分享
  • 克里斯汀·曹
    克里斯汀·曹 ✭

    @Matt约翰逊我按照你的建议做了这件事，并将监视，看看这是否解决了问题。再次感谢您的建议。学到了一些非常有用的新东西。——克里斯汀

    · 在Facebook上分享 在Twitter上分享

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>
\n \n https:\/\/youtu.be\/Dzo0UYjxMmI\n <\/a>\n<\/div>


<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-11T19:20:33+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T21:34:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-12T03:09:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":57,"score":null,"hot":3372844251,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380011,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380011#Comment_380011","dateInserted":"2023-06-11T19:20:33+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-12T03:09:32+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"

If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>

=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>


<\/p>

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106286,"type":"question","name":"Health Bubble Turn Colors when Status is Updated","excerpt":"Hello, I am new to Smartsheet. I am trying to get when I change my Status to \"Not Started\" I want the Health bubble to automatically turn grey. And when I change my Status to \"In Progress\" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = \"Not Started\", \"Gray\", \" \"). I want it to change color for each…","categoryID":322,"dateInserted":"2023-06-09T19:29:22+00:00","dateUpdated":"2023-06-09T20:02:36+00:00","dateLastComment":"2023-06-09T21:14:52+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-10T18:54:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162263,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":34,"score":null,"hot":3372684854,"url":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","format":"Rich","tagIDs":[254,440],"lastPost":{"discussionID":106286,"commentID":379933,"name":"Re: Health Bubble Turn Colors when Status is Updated","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379933#Comment_379933","dateInserted":"2023-06-09T21:14:52+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+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-10T18:54:41+00:00","dateAnswered":"2023-06-09T21:14:52+00:00","acceptedAnswers":[{"commentID":379933,"body":"

You will need a nested IF formula:<\/p>

=IF(Status@row=\"Not Started\", \"Gray\", IF(Status@row=\"In Progress\", \"Yellow\", IF(Status@row=\"Complete\", \"Green\")))<\/p>


<\/p>

Just add more IF Statements like above for what you need. Put all ) at the very end. Then you can turn it into a column formula by right clicking the cell the formula is in and selecting column formula.<\/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"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 遇到了父单元格聚合子任务名称的公式。

    我想捕获单个项目名称，由子任务跟踪，并将它们聚合在父单元格(所有项目名称)中。这样，当最小化时，我就不必展开来查看任务是针对哪些站点。同样对于Dashboard视图，查看哪些站点是被跟踪的父项目的一部分。截图……

  • 我如何创建和如果公式从另一个工作表拉来指定指标?

    大家早上好，我正在处理一个项目的整体运行状况，它看起来像这样:我需要通过使用if语句来更新灯光，该语句查看另一张工作表中三个不同的列，这些列在工作表中并不相邻，下面是该工作表的示例。在第一张工作表中，对于每个列，我需要查看…

  • 当状态更新时，生命泡泡会变颜色

    你好，我是Smartsheet的新手。我试图得到当我改变我的状态为“未开始”，我想要的健康气泡自动变成灰色。当我将自己的状态更改为“进行中”时，生命值气泡将变成黄色，等等。到目前为止，我有:IF(Status1 = "Not Started"， "Gray"， " ")。我想让它改变颜色…