This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a scenario where i need to generate rows starting from a date field and working backwards.
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.
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.
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.
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.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
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.