Line Graph Grouped By Hour

[brett.adlard]

Hello,

I am trying to create a line graph from data pulled using an API. The data returned from the api is of two columns; a date time in millis utc and a price. Currently the date time column is prices every 5 minutes but I want to aggregate the five minute prices into an average price over the hour.

When I try to group by and aggregate I get 3 options to aggregate on (exact, day, month). Is there a way to aggregate by the hour? I could try creating a new column from the api which pulls the hour from the date then aggregate by that column but I’m not sure if that’s possible or how I would do that.

Any help would be appreciated!

Thank you

[helpful-pixel]

Have you come across this thread? It helped me understand how to solve a similar issue on an app I developed a while back…

[New Feature] Grouping and Aggregating Data

Hi all, We’ve heard from many of you that you’d like an efficient way of displaying your app’s data in a chart or table. For instance, maybe you’d like to be able to show a graph of new user signups by month, or show a table of total sales by region and account. We now have a tool for supporting this kind of operation. It’s called “:group by”, and you apply it on top of a search or other list of data items in order to group and summarize the list. [group%20by%20screenshot] The basic conce…

[brett4]

Thanks for your response. I did see this thread. I saw someone else ask a similar question but it does not appear anyone answered them.

Anything else I should be considering?

[helpful-pixel]

There are a few ways to approach this, so don’t think this is the only way:

I manage an app where data comes in 24 times a day for each location, but I only need to show a daily overview on the dashboard/charts.

I didn’t want to delete my 24 data points in case they’re helpful in future, but I also didn’t want my app calculating averages every time the data is pulled … even 24 data points x 30 days in a month was slow for my users.

For this reason, the workflow that brings in data runs a custom event on the new data to calculate the daily overview value, which is not so different than average of the 24 data points.

Due to type of data I’m working with I send the 24 daily points via API to https://api.mathjs.org/ and store the returned calculated value. My charts then ignore the 24 granular data points that were originally stored and only show the calculated/stored value - this is a lot faster for my use case.

Working this way also allows me to check my work later, as I link items using their unique IDs to help me ensure everything is working as designed.

[brett.adlard]

Wow. Thank you so much. I learned a lot from this in depth answer and I really like your use of this mathjs API. I. think that could be super helpful.

I’m going to give it some more thought with this new information and see where I can get to!

Thanks!