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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Breaking down aggregated downtime into shifts

Shelbey
Alteryx
Alteryx

Good morning Community! I'm hoping somebody can help me with my workflow. I thought I almost had it, but I'm stuck, and I don't know if I'm making it more complicated than it should be.

 

I have a data set of equipment downtime. Each record shows the start date and time of when the equipment went down and the end date and time of when it came back up. There's also a second end date field where nulls mean the equipment is still down right now. What I am trying to do is break down the total downtime shown in each record into a daily downtime when the downtime exceeds 1 day. To make things more complicated, everything my company does is by the shift (6 a.m. to 6 p.m. is day shift, and 6 p.m. to 6 a.m. is night shift), so instead of just tracking the daily downtime for each equipment, I have to break it down even further into the shifts of each day.

 

Some of the rows are really easy and the downtime starts and ends on the same day and the same shift. Some rows start and end on the same day but across two different shifts. Some start and end multiple days and shifts apart. I tried using a Generate Rows tool to generate rows according to how many shifts were in the total downtime (I took the total downtime divided by 12) but that didn't always work because you can have 7 hours total of downtime but across two different shifts depending on the time the downtime actually started but my formula will only return 1 shift. I continued working from that formula though and was going to fix the 'exceptions to the rule' at the end, but now my first Generate Rows tool is not looping the expression when I haven't made any changes to it since the last time I tried running it and it was working. So now I figured I better ask for help because I'm starting to feel defeated. 

 

If anybody can help me, it would be greatly appreciated. 

6 REPLIES 6
DavidP
17 - Castor
17 - Castor

I haven't looked at your logic yet, but I think the problem with the Generate rows tool is that it changed from updating existing field to Create New field [RowCount]

 

As [RowCount] does not feature in the loop expression, it's value is not updated. I'm guessing that it should be updating [# of shifts].

 

I'll mull it over and see if I can come up with a suggestion, but others will probably beat me to it.

DavidP
17 - Castor
17 - Castor

I don't know if this helps you in any way. If you map out what the format of the desired outcome is, that would be very helpful.

 

This is far from complete, but maybe it helps you in the right direction. I'll play with it some more and let you know if I get any further.

 

I'm using generate rows to create a list of shift start times from the 1st of Jan and give them a shift number.

 

In the main dataset I then find the closest shift start time to both the Start Date and End Date an join both to the generated list to find the shift number for each start and end date. This shows you how many shifts the downtime spans.

 

 

DavidP_0-1586882753618.png

 

DavidP
17 - Castor
17 - Castor

workflow attached

Shelbey
Alteryx
Alteryx

Thank you! I'm going to look through it right now.

RolandSchubert
16 - Nebula
16 - Nebula

Hi @Shelbey ,

 

I think, a slightly different approach would be possible. Hopefully I didn't oversimplify the problem ... in the attached sample workflow I used mainly date time formulas to calculate the downtime within a shift. Let me know, if it works for you.

 

Best,

 

Roland

Shelbey
Alteryx
Alteryx

Thank you!  I was actually able to use modified versions of both your solutions.

Labels