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.

Split Total minutes (In and out Time) in 15-minute Time Frame

8 - Asteroid

Hi All,


I have four columns Date, In-time, Out-time, and Total Minutes. Further, I would like to create 15-min time frame columns and divide total minutes in those 15-minute time frame columns. I'd highly appreciate if you could suggest on how to achieve that?


 Annotation 2019-07-25 140837.png


I have attached the excel file of the image.



14 - Magnetar
14 - Magnetar

Fun one! This one can be done with a few key tools: Generate Rows (to find each minute between In & Out), a Formula to determine which "Time Bucket" each minute belongs to, and then Summarize tool to group by Time Buckets and count the number of minutes in each. Also included some functionality to get you the whole list of possible time buckets (used the same start and finish as your example data, but could easily be adjusted). 




Hope this helps! 




14 - Magnetar
14 - Magnetar

Hi @Shivam 


Here's another approach to your challenge. I reverted the Excel file back to the original 4 columns you had mentioned (first Select tool), and then used a formula tool to generate the first time grouping. A Generate Rows tool generates the 15 minute increments into the times you need into a field called [TimeStamp] (I just based this on the times in the Excel file).


Then, a Formula tool does the heavy lifting. If the [In Time] is greater than the [TimeStamp], then there's nothing to calculate. Otherwise, if the [TimeStamp] is less than the [Out Time] which captures when the total minutes should start, calculate the difference between [TimeStamp] and [In Time] in minutes. Otherwise, subtract the difference between [TimeStamp] and [Out Date] from 15. image.png

Zeros where the time grouping is before the In Time, calculated time difference when it's in range, and negative numbers where the time grouping is after both the In Time and Out Time.

A little more cleanup within the tool, recalculating the Minutes so that if they are greater than 15, they become 15 (the largest allowable interval), and replacing negative numbers with zero.

Next, use a DateTime tool to convert the TimeStamp (which will be used as columns in the crosstab) to text, removing the date piece.

Crosstab everything so that the intervals create the columns. And Dynamic Rename to replace the _ with :


Let me know if this helps.




8 - Asteroid

Thank you @EstherB47  and @NicoleJohnson.  Both the solutions work perfectly for me!

5 - Atom




Can you send this in a power query view or with the Power Query formulas that are needed?  I'm not able to decipher the information from the xml file.