How to get automatically an average data based on date rage on a daily basis

Marcin4q

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

Nick Korna

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! 😊

Nick Korna

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! 😊

Marcin4q

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?🙏

Marcin4q

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?

Marcin4q

One more question, Is there a way to automate refresh? to make sure it's up to date before sending out?

Nick Korna

https://community.smartsheet.com/discussion/comment/389661#Comment_389661

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.

https://community.smartsheet.com/discussion/comment/389671#Comment_389671

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.

https://community.smartsheet.com/discussion/comment/389676#Comment_389676

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. 😊

Marcin4q

https://community.smartsheet.com/discussion/comment/389711#Comment_389711

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

Nick Korna

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!