Scrolled Values Calculation

[almartinez]

Hello Team,

I need to figure out a way to calculate the last day to perform an activity which involves counting Weekends and Holidays if the calculated value (LAST DAY TO PERFORM) has passed through any of these two criterias, they need to be added to the calculation.

I have attached a simple module illustrating the case.

If you could please help it would much appreciated.

Kind Regards Team,

[Federica_FF]

Hi @almartinez,

I think an easy way to get your result is to think "vertically".

Using the generate rows tool you can create N rows for each activity with the sequence of the days, a sort of calendar.

RUN - 2017-08-08

RUN - 2017-08-09

RUN - 2017-08-10

RUN - 2017-08-11

RUN - 2017-08-12

RUN - 2017-08-13

RUN - 2017-08-14

RUN - 2017-08-15

RUN - 2017-08-16

RUN - 2017-08-17

RUN - 2017-08-18

RUN - 2017-08-19

RUN - 2017-08-20

RUN - 2017-08-21

RUN - 2017-08-22

RUN - 2017-08-23

RUN - 2017-08-24

RUN - 2017-08-25

etc...

Then filter away weekends and holidays

RUN - 2017-08-08

RUN - 2017-08-09

RUN - 2017-08-10

RUN - 2017-08-11

RUN - 2017-08-14

RUN - 2017-08-16

RUN - 2017-08-17

RUN - 2017-08-18

RUN - 2017-08-21

RUN - 2017-08-22

RUN - 2017-08-23

RUN - 2017-08-24

RUN - 2017-08-25

etc...

And then only keep the first N rows for each activity, where N is the duration day field +1 (because the calendar starts from the first day of the activity date but we usually count starting from the second one)

RUN - 2017-08-08 - 1

RUN - 2017-08-09 - 2

RUN - 2017-08-10 - 3

RUN - 2017-08-11 - 4

RUN - 2017-08-14 - 5

RUN - 2017-08-16 - 6

RUN - 2017-08-17 - 7

RUN - 2017-08-18 - 8

RUN - 2017-08-21 - 9

Then you can use a summarize tool to group by activity and getting the MAX of the date 2017-08-21 that will be your last day to perform.

I've attached a workflow as example. It's just a starting point, you can edit and suit it to fit your needs.

[almartinez]

Hi @Federica_FF

it worked fine! I really appreciate your feedback on this. I was wondering, is there any way to calculate the number of days between the Activity date and the Last day to Perform? Using DateTimeDiff() gives me the total number of days (including Weekends too) therefore the calculation is always bigger instead of excluding them.

Thank you in advanced!

[Federica_FF]

Cool! I'm happy you found my example useful.

You could try to summarize your data and this time count the rows (days) instead of asking for the MAX. Just edit the last summarize tool, count instead of max

Using the filters to only leave the useful days, if you count how many rows/day you have, you should get the difference.