community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Generate rows - backwards dates without weekends

Meteor

Hello,

 

I have a scenario where i need to generate rows starting from a date field and working backwards. 

 

For example:

Completion Date = Fri March 16th

I need to create 3 rows to represent 3 days of estimated work that will hit that Completion Date.  In this case- not a problem to increment backwards until the 3 days are reached.

 

However, weekends are giving me an issue. I cannot count them in my logic.

 

Ex.

Completion Date = Tuesday March 20th

I need to back off 5 days (or 10, covering 2 weekends....).  That will end up only going back to Thur March 15th because it counted Sat/Sun. It should have created rows back to Tue March 13th excluding Sat/Sun.

 

The Generate Rows function doesn't seem to allow me to do this.  Ideally, I'd love to say "generate xx number of rows where the date doesn't land on Sat or Sun".  But I don't see how to do this and still have a date value generated per row.

 

Any ideas?

 

Thanks!

Alteryx Certified Partner
Alteryx Certified Partner
Try building logic for number of potential days as:

Divide by seven and use the ceil() function to round up. Take that result and multiply it by two. That's the number of max weekend days to add.

So now you have too many days. So filter out days that are in Saturday or Sunday by use of a date time format looking for the day of the week (%A) in an in() function.

You can use a multirow formula to count the number of desired days. Filter out any extras.

From the airport, without my laptop, this would be my back of the napkin approach to start your exercise with.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

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

Mark,

 

Thanks for your quick reply. I can see how you approached that and it makes sense.  However, if I'm reading it correctly, that would only work if the numbers of days might actually overlap a weekend.  For example, I could not use that as a generic approach for a scenario where the Completion Date is a Friday and I want to back off 3 days.  No weekend impact in this case.  It sounds like I'd need to build in first-layer of logic that says if the day is X and the days to subtract are Y AND that could touch/cross a weekend then use the approach you noted.

 

One thing I did finally realize is that i can control the loop iterations to skip weekends using IF THEN ELSEIF.  Unfortunately, I still don't have a way to say "here is the date you must to go to", so that loop logic doesn't help much at this point.

 

Thanks,

 

Nick

Alteryx Certified Partner
Alteryx Certified Partner
In use of the multi row formula you can group by the key and count the number of rows. That rowid can be checked against the days field and filtered out. Remember to keep the rows in date descending order.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

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

Here is a different way to solve this.  Takes account of the fact that the days of the week that are weekend days both start with "S".

 

2018-03-13_00019.png

Alteryx Certified Partner
Alteryx Certified Partner

Interesting problem.

 

Here is how I have resolved.

 

Basiscally I used the formula ceil(([Days Taken]/5))*7 to work out the maximum number of days it could have taken to complete the job if we include weekends.

I then generate the dates for all of these look backs and filter out values where the part of the day is saturday or sunday. I then do a running total of days and exclude any values which is greater than the true number of days taken for the job.

 

See attached workflow.

 

Ben

Alteryx Certified Partner
Alteryx Certified Partner

Sorry I have inadvertently created pretty much the same solution mentioned by @MarqueeCrew

Anyway, the attached workflow to my post should put his vision into action!


Ben 

Meteor

Thanks all!  This community is great!

 

I appreciate the help with this. I need to start learning the multi-row tool it would seem....

 

Thanks!

 

 

Labels