Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #10: Date Time Calculations

GeneR
Alteryx Alumni (Retired)

Here is this week’s challenge, I would like to thank everyone for playing along and for your feedback. The link to the solution for last challenge #09 is HERE. For this challenge let’s look at the date and time functions in Alteryx.

 

The use case:
A distribution center receives a package. At the point of receipt, the package is scanned and a timestamp is captured for arrival date/time. The company is trying to reduce the amount of time the package is at its facility and as a result is trying to analyze how long packages remain at the facility.

 

The objective for this challenge is to calculate the delta between arrival date/time and the Time_Now field (this field has the date/time of the creation of this challenge and will be changing as time goes on). Create a unique field for Days, Hours, Minutes and Seconds.

 

We have listed this as an intermediate exercise  and I expect it will go very quickly for many of you. Let us know what you think, we are looking forward to hearing your feedback.

 

UPDATE 2/1/2016

You can find the solution below.

alex
11 - Bolide

Just a heads up, since the Time_Now stamp is slightly different in the input file vs. the output file, 2014-07-14 08:24:35 vs. 2014-07-14 08:25:16, the answers will be off by 41 seconds if you are verifying results against the output file.Smiley Happy

 

TaraM
Alteryx Alumni (Retired)

A solution to this exercise has been posted.

Tara McCoy
alex
11 - Bolide

I went with the following formulas and solved it correctly using one less formula than the posted answer. 

 

 

Spoiler

Days = DateTimeDiff([Time_Now],[TIMESTAMP],"days")

Hours = DateTimeDiff([Time_Now],[TIMESTAMP],"hours")-[Days]*24
Minutes = DateTimeDiff([Time_Now],[TIMESTAMP],"minutes")-DateTimeDiff([Time_Now],[TIMESTAMP],"hours")*60
Seconds = DateTimeDiff([Time_Now],[TIMESTAMP],"seconds")-DateTimeDiff([Time_Now],[TIMESTAMP],"minutes")*60

mceleavey
17 - Castor
17 - Castor

A simple modulo formula did the trick:

 

Spoiler
Solution.PNG

Here, I simply created a straight datetimediff calculation for the Days, and modulo differences for the others:

Hours - mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"hours"),24)
Minutes - mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"minutes"),60)
mod(DateTimeDiff( [Time_Now],[TIMESTAMP],"seconds"),60)

Results.PNG


Simple.



Bulien

MarqueeCrew
20 - Arcturus
20 - Arcturus

I'm off to basecamp :)

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
SeanAdams
17 - Castor
17 - Castor

Very similar to all the others

Spoiler
Didn't use a mod formula, but used the floor function to trim off the decimal places.

Also, broke it out a little to make the workings a little more obvious - used a set of raw fields to hold total days, hours, minutes, seconds for the entire span, and then just trimmed back from this using a floor function.


 

NicoleJohnson
ACE Emeritus
ACE Emeritus

My solution. :)

 

Spoiler
WeeklyChallenge10.JPG
Laurap1228
11 - Bolide

Challenge #10 done in one tool!!

 

Spoiler
challenge10.jpg
estherb47
15 - Aurora
15 - Aurora

also did this with one formula tool