How to get automatically an average data based on date rage on a daily basis
We are using form to create performane data per robot per run ( input can be few times a day)
I need to start measuring daily avereage for their performance seperately (1 called North 1 called South) but can't figure out formula for it and then I'm assuming creating a chart would be straightforward? but not done it before so any guidence would be appriciated especialy to to do chart on rolling 7 days
Best Answer
-
Hi @Marcin4q,
To get your desired 7 days data you can do this using a report based on your sheet.
Create the report and pick your data sheet.
For columns you would want the Created, Robot No & Total Effectiveness Columns.
You can then filter using the Created column with the criteria being the (created) date being within the last 7 days. You can also group by Robot No to see the data in the report directly and have it prepared for creating a chart widget in a dashboard.
Some (simplified samples) of how it'd look:
Hope this helps somewhat - if there is anything you're not sure of or I've misunderstood what you're after just post and we should be able to fix it! 😊
Answers
-
Hi @Marcin4q,
To get your desired 7 days data you can do this using a report based on your sheet.
Create the report and pick your data sheet.
For columns you would want the Created, Robot No & Total Effectiveness Columns.
You can then filter using the Created column with the criteria being the (created) date being within the last 7 days. You can also group by Robot No to see the data in the report directly and have it prepared for creating a chart widget in a dashboard.
Some (simplified samples) of how it'd look:
Hope this helps somewhat - if there is anything you're not sure of or I've misunderstood what you're after just post and we should be able to fix it! 😊
-
You are a superstar!, this is exactly what I needed. Is there a way of automating the report so lets say it sends the report on email on a daily basis for previouse day result and another one on Monday morning for last 7 day?🙏
-
Also I have multiple inputs for 1 day that I would like to change that in to get the average for the day so I can get a daily average chart for 7 days, Is that possible?
-
One more question, Is there a way to automate refresh? to make sure it's up to date before sending out?
-
You can create 2 reports - one for daily & one for 7 days.
Reports don't use automation as such, but when you pick "Send as an attachment" you can set a schedule to accomplish much the same thing.
If you want daily averages rather than every single reading, then I would suggest making some sheet summaries to capture each of these for the 7 days and then for your weekly report use a sheet summary report instead of a row one. You can also do this for the daily for data consistency.
The reports should automatically refresh with any additional data put in on the sheets so barring literally putting anything in seconds before your emails are sent things should be fine. 😊
-
On the email report I can't see the summary like I do on the actual report.
Is there a way of changing it so it does show on the email report?
Best regards
Marcin
-
I can't seem to find a way to have it display in the same way. You can have the daily average for a robot on the main sheet with the aid of either a helper column and sheet summaries or two columns.
For example using the daily as an example
With the former you can use set and leave a filter with date as today to email the sheet rather than needing a report and the summaries will have the averages calculated, with the latter you can have the column itself show up on the report (though it will have the average displayed for every line).
Helper column:
=IF(AND(Date@row = TODAY(), [Robot No]@row = "North"), 1, IF(AND(Date@row = TODAY(), [Robot No]@row = "South"), 2, ""))
Sheet summary using this (this is for North, you would need a second for South changing the bolded 1 to 2):
=AVERAGEIF(Helper:Helper, 1, [Total Effectiveness]:[Total Effectiveness]) * 100
This will give you decimals rather than percentages, but you can easily change this if you're picky:
=(AVERAGEIF(Helper:Helper, 1, [Total Effectiveness]:[Total Effectiveness]) * 100) + "%"
If you went with the daily average column, then a formula like this would do it:
=IF(AND(Date@row = TODAY(), [Robot No]@row = "North"), AVERAGEIF(Helper:Helper, 1, [Total Effectiveness]:[Total Effectiveness]), IF(AND(Date@row = TODAY(), [Robot No]@row = "South"), AVERAGEIF(Helper:Helper, 2, [Total Effectiveness]:[Total Effectiveness]), ""))
On the sheet (with filter for today applied and the extra column in for the report and North showing as percentage and South as the decimal only):
On the report:
The email of the sheet has 3 tabs (sheet, comments, summary), so you can find the average at a glance with this, or have it all in a single place with using the extra column (either on sheet or in the report, though without the filter the sheet will show all lines).
Alternatively you can publish the report which does keep the summaries, but then requires someone to go to the link or to have it embedded somewhere - this may not be the best solution for at a glance or if someone is looking back for previous days.
Hopefully this helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
