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 #106: How Long were the Lights On?

Jean-Balteryx
16 - Nebula
16 - Nebula

Here is my solution !

erick_irigoyen
Alteryx Alumni (Retired)

Solution attached

T_Willins
14 - Magnetar
14 - Magnetar

Reposting to add spoiler (to get challenge tracker to work correctly)

 

Went down a rabbit hole at first trying to use multi-row formulas to eliminate overlapping end and start times and coming up with the differences between combined enter and exit times.  Got within one minute of the answer, but realized would take several multi row formulas to achieve the correct result.  Realized the need to find unique times and came up with a similar workflow to the others.

 

Spoiler
Workflow 106.JPG

 

myouree
8 - Asteroid

My solution

mat_budden
8 - Asteroid

Hi All,

 

Please find my Result below.

 

Spoiler
Time Parse to get into Times, Then Multirow formulas to workout the overlaps, Formulas to correct then Get the minutes, Then summarize.

 

Thanks

Mat

dsmdavid
11 - Bolide

Can finally attach files, although cannot edit my previous reply

RoDO
8 - Asteroid

My solution

 

Spoiler
challenge_106_RODO_Solution.png
Billigans
8 - Asteroid
Spoiler
challenge_106_solution_BL.png

rslopez2
8 - Asteroid

Good practice with Multi-Row Formula Tool!

 

1) Parse given date fields to calculate elapsed time in room

2) Sort by Room and Enter time to get chronological entry times

3) Calculation to flag if subsequent enter time is before previous exit( flag overlap)

3) Calculation to determine of subsequent exit time extends time in room (and adds overall light time) or if exit prior to previous entry(denoted by negative number)

4) Eliminate negative time records to account for subsequent exits prior to previous.

5) Summarize

 

Spoiler
RL_WK_106.png
AmeliaG
Alteryx
Alteryx
Spoiler
106_screenshot.png