表单条目的报告/仪表板
我们的转介部门最近开始使用智能表格来跟踪每个转介到我们安宁疗护的关键信息。每个团队成员在获得推荐后完成并提交表格。我有工作表设置为自动日期/时间戳每一行，因为它进来。部门主管要求我做一些报告，我想把这些报告显示在仪表板上。以下是我们希望获取的两个主要数据点。
1:能够看到每月有多少推荐提交。
2:能够跟踪每个团队成员和上个月到本月提交了多少推荐。
我已经创建了几个报告，但是它们需要每个月手动更新才能更新这个月。寻找更自动化的东西。
欢迎大家推荐!谢谢你！
最好的答案
-
奥斯汀史密斯 ✭✭✭✭
最快，最简单的方法是:
在主页上建立一个助手列，列公式=month()(电子邮件保护)）
1)在汇总数据或另一张表的helper列中对月份进行求和
2）
使用公式=countifs(created:created， =month(today())， [team member]:[team member]，“Laura”)建立一个汇总数据表——它给出了Laura这个月有多少推荐人。
摘要数据表还有一个列=countifs(created:created， =(month(today()-1))， [team member]:[team member]，“Laura”)，它给出了她上个月的数字
拉出汇总数据表上的报告来显示比较。
-
奥斯汀史密斯 ✭✭✭✭
答案
-
奥斯汀史密斯 ✭✭✭✭
最快，最简单的方法是:
在主页上建立一个助手列，列公式=month()(电子邮件保护)）
1)在汇总数据或另一张表的helper列中对月份进行求和
2）
使用公式=countifs(created:created， =month(today())， [team member]:[team member]，“Laura”)建立一个汇总数据表——它给出了Laura这个月有多少推荐人。
摘要数据表还有一个列=countifs(created:created， =(month(today()-1))， [team member]:[team member]，“Laura”)，它给出了她上个月的数字
拉出汇总数据表上的报告来显示比较。
-
玛丽莎·T。 ✭@Austin史密斯非常感谢!这是有效的，除了当我输入第二个带有-1的公式来计算前一个月时，由于某种原因-1似乎没有注册，它仍然在计算当前月份。你对为什么会出现这种情况有什么建议或想法吗?
再次感谢!
-
奥斯汀史密斯 ✭✭✭✭
帮助文章参考资料欧宝体育app官方888
类别
=COUNTIFS({状態 Status}, OR(@cell = \"未着手 Not Started\", @cell = \"進行中 In Progress\", @cell = \"現場確認 Field Review\", @cell = \"保留中（予算）On Hold (Funding)\", @cell = \"保留中 On Hold\"), {エリア Area}, @cell = \"Example1\")<\/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"}]},{"discussionID":105974,"type":"question","name":"Help creating a formula that generates the current fiscal year","excerpt":"The following formula is #UNPARSEABLE. I'm not sure why. Please help. =IF(MONTH(TODAY())>=7, YEAR(TODAY())&\"-\"&(YEAR(TODAY())+1), (YEAR(TODAY())-1)&\"-\"&YEAR(TODAY()))","categoryID":322,"dateInserted":"2023-06-02T19:06:56+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161705,"lastUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3371469191,"url":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105974\/help-creating-a-formula-that-generates-the-current-fiscal-year","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105974,"commentID":378672,"name":"Re: Help creating a formula that generates the current fiscal year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378672#Comment_378672","dateInserted":"2023-06-02T19:46:15+00:00","insertUserID":161705,"insertUser":{"userID":161705,"name":"Christina S.","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:44:40+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-02T19:45:43+00:00","dateAnswered":"2023-06-02T19:21:45+00:00","acceptedAnswers":[{"commentID":378667,"body":"
The concatenate operator in Smartsheet is + instead of &.<\/p>
<\/p>
=IF(MONTH(TODAY())>=7, YEAR(TODAY())+\"-\"+(YEAR(TODAY())+1), (YEAR(TODAY())-1)+\"-\"+YEAR(TODAY()))<\/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"}]},{"discussionID":105970,"type":"question","name":"Trying to evaluate multiple columns with an IF statement","excerpt":"I keep getting a syntax error for some reason but can't figure out why. I am basically trying to set a status icon of 'Sunny\" if the status of the risk is \"closed\" and if not closed, i am using the other icons based on the level of the risk. here is the formula: =IF([Status]@row = “Closed”, “sunny”, IF([Risk Score]@row <=…","categoryID":322,"dateInserted":"2023-06-02T17:58:00+00:00","dateUpdated":null,"dateLastComment":"2023-06-02T19:19:11+00:00","insertUserID":162035,"insertUser":{"userID":162035,"name":"Drew N.","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Drew%20N.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-02T19:20:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-02T20:54:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3371464031,"url":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/105970\/trying-to-evaluate-multiple-columns-with-an-if-statement","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":105970,"commentID":378663,"name":"Re: Trying to evaluate multiple columns with an IF statement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/378663#Comment_378663","dateInserted":"2023-06-02T19:19:11+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-02T20:54:24+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-02T18:44:44+00:00","dateAnswered":"2023-06-02T18:24:38+00:00","acceptedAnswers":[{"commentID":378641,"body":"
I think you are overcomplicating it. Try this instead:<\/p>
=IF([Status]@row = \"Closed\", \"sunny\", IF([Risk Score]@row <= 25, \"partly sunny\", IF([Risk Score]@row <= 50, \"cloudy\", IF([Risk Score]@row <= 90, \"rainy\", \"stormy\"))))<\/p>
<\/p>
It also looks like some of your quotes are slanted and others are straight up and down. The slanted ones are called \"Smart Quotes\" which (ironically enough) are not recognized as valid characters in Smartsheet formulas. You will need to either retype them here in the Community (which I did above already), directly in Smartsheet, or in a text editor such as Notepad (not Word).<\/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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
