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

Generate rows - backwards dates without weekends

nsmith293
7 - 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!

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
nsmith293
7 - 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

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
derekbelyea
12 - Quasar

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

BenMoss
ACE Emeritus
ACE Emeritus

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

BenMoss
ACE Emeritus
ACE Emeritus

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 

nsmith293
7 - 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