Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Summing by Disctinct Time Ranges

Highlighted
5 - Atom

Hello,

 

I'm trying to create time intervals to group concurrent user data. I have data on the start time hour and end time hour with the sums of users that match both criteria. The start time of January-01 00:00, for example, has record counts that match with 00:00, 01:00 and 02:00 end times. My required output are "Hour Start" and "Hour End" columns organized into 1 hour intervals.

 

Current Output 

 

Hour Start             Hour End              Sum_Concurrent Users
January-01 00:00 January-01 00:00 8
January-01 00:00 January-01 01:00 76
January-01 00:00 January-01 02:00 6

 

Desired Output

 

Hour Start             Hour End              Sum_Concurrent Users
January-01 00:00 January-01 01:00 ___
January-01 01:00 January-01 02:00 ___
January-01 02:00 January-01 03:00 ___

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@iuduebo ,

 

if you first convert your text date start and stop to the format of:  YYYY-MM-DD HH:MM:00

 

 you can use this macro to create every minute records in the range.

 

https://gallery.alteryx.com/#!app/Generate-Date-Rows/5e31f42a826fd30ffcbf2c14  

 

Now you can join to the table 1 data.  This will cause your range to be on each joined record. Now you can simply use a summarize tool to group by your range and SUM the values. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels