Summarize timeseries table

Hi,

I am very new to Alteryx!! I came across a scenario where I need to summarize a table based on Date and time series. My input data is as below and is a very huge file of which I need the output data only for a specified date.

Input 1.JPG

My output file should be in the format as given below. I need to summarize the data for a specific date and time in 15 minutes for each area. Can anyone help to solve this?

Topic of Interest

Accepted answers

Joe_Mako

How about a formula to Bin Time like:

DateTimeAdd(DateTimeTrim([Actual_DateTime],"hour"),
Floor(DateTimeMinutes([Actual_DateTime])/15)*15,"Minutes")

What this expression does:

- Round the time down to the hour with DateTimeTrim

- Get the Minutes value with DateTimeMinutes

- Bin the Minutes by 15 with a Floor of the number of Minutes divided by 15, and the result of the flooring multiplied by 15

- Add the Binned Minutes to the rounded down time with DateTimeAdd

Then you can use a Summarize tool to group by this time and your Area, attached is an example.

Time Bin.yxmd

All comments

Joe_Mako

How about a formula to Bin Time like:

DateTimeAdd(DateTimeTrim([Actual_DateTime],"hour"),
Floor(DateTimeMinutes([Actual_DateTime])/15)*15,"Minutes")

What this expression does:

- Round the time down to the hour with DateTimeTrim

- Get the Minutes value with DateTimeMinutes

- Bin the Minutes by 15 with a Floor of the number of Minutes divided by 15, and the result of the flooring multiplied by 15

- Add the Binned Minutes to the rounded down time with DateTimeAdd

Then you can use a Summarize tool to group by this time and your Area, attached is an example.

Time Bin.yxmd

Zudi

Hi Joe,

Thank you for the solution. Its working perfectly fine with a small data set. But when I connect to a large data set the below pop up appears. Any idea why this is happening? Greatly appreciate your help!!

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335