Title: How to aggregate and graph the number of calls by creation time in Smartsheet
first
This article provides answers to questions posed by Smartsheet community members.The questioner was looking for a way to use Smartsheet to aggregate data by creation time and graph it like an Excel pivot table.
User question summary
The user has a "Create" row that is automatically created every time a new call log is submitted, and creates a graph that sums up all calls by hour of the day, specifically by hour. I would like to create one. In Excel, I want to create a pivot on the "Create" column and group by time to create a chart, but I can't figure out how to accomplish this in Smartsheet.
Summary of my answer
To get the time part of "Creation date and time" in 24-hour format, use the following formula:
=TIME(RIGHT(Created@row, 8), 1)Then you can get the "time" part with the formula below.
=VALUE(LEFT([24 Hour Format]@row, 2))You can use metric sheets to count data groupings over time and create charts within your dashboard.
Body: Question and answer sequence
sstubbs ✭
2023/10/31
I don't know how to achieve this with Smartsheet.There is a call log that is automatically generated every time a new "create" line is submitted.Specifically, I would like to create a graph that sums up all calls by hour of the day.
In Excel, I want to create a pivot on the "Create" column and group by time to create a chart, but how can I do that?
jmyzk_cloudsmart_en ✭✭✭✭✭
October 2023, 10 Answer ✓
Hello @sstubbs
You can get the time part of "Creation date and time" in 24-hour format with the following formula;
=TIME(RIGHT(Created@row, 8), 1)Then, you can get the "time" part with the following formula;
=VALUE(LEFT([24 Hour Format]@row, 2))Link to demo dashboard
You can create charts within your dashboard using metric sheets that count data groupings over time. 😁
sstubbs ✭
2023/11/1
@jmyzk_cloudsmart_jp Your solution worked.
jmyzk_cloudsmart_en ✭✭✭✭✭
2023/11/1
@sstubbs
Oh, your dashboard is beautiful!
Conclusion
Using my experience with Excel, I was able to find a solution to the question of wanting to perform similar data aggregation and graphing in Smartsheet by providing a specific formula. Smartsheet also allows you to efficiently process data and customize beautiful dashboards.
[Demo Dashboard]: https://app.smartsheet.com/b/publish?EQBCT=9561450106084517a0027e6fd2b37b09&_ga=2.107808197.67690267.1699359440-1636588354.1692577300&_gl=1*1b9kt4c*_ga*MTYzNjU4ODM1NC4xNjkyNTc3MzAw*_ga_ZYH7XNXMZK*MTY5OTQxNzQ3OS4xNjguMS4xNjk5NDE5NTkwLjYwLjAuMA..