Loop checking and forecasting data for holidays

[Zynk]

I have a case where i have to create a report everyday for the items that are on their last day of completion for that day. The thing is since our working days are from mon-fri . On friday we have to compile reports that also shows the items that needs to be completed over the weekend or any public holiday. 

For example:

There are three items namely abc,def,ghi that needs to be completed today. Today is lets say "monday"

I will send an email out that the below items are to be completed today: 

abc, def, ghi

The same continues until friday comes. On friday i have items namely 12a,13a,14a , and i can also see items that needs to be completed on saturday (namely 14b,15b,16b) and sunday (14c,15c,16c)  (but no one is working on  those days hence i need to send all the items for fri,sat and sun on friday email. hence i will send the below email

There are three items namely 12a,13a,14a, 14b,15b,16b, 14c,15c,16c  that needs to be completed today

Now i have built a counter that assigns an indicator for items from mon-thur and another for fri items that inculed next three day items. What i want to do is include public holidays since i have an app that can calculate for sat and sun. But what i want to do is use a loop for the app to automatically check the number of off days in advance. Lets say there is off on sat and sun and a public holiday on mon and tue. My app can calculate for sat and sun but for mon and tue i have to check manually the items for those days that needs to be completed before the off-day period begins. i want my loop to automatically pull items for the recurring days which are holidays instead of checking manually.  

The idea i have is to create a file which contains all holidays and sats suns that are there in a year and use this file so as the app matches the dates of the week in advance to see is there are recurring holidays on the next day from which the items are sent.

I would like if anyone can provide a logic as to how i can proceed to obtain this feature.

thank you

[JohnGD]

Hi @Zynk !

This sounds like an interesting case !

What i suggest is that once you have these data sets ready :

- public holidays

- items + last day of completion

you join the two together so you can flag the last completion days that are public holidays.

using a Multi-Row Formula Tool you can write a condition to check if the next coming up days are business days.

(if not, you can flag these public holidays / weekends for grouping to the closest business day).

This article could be at help with this tool : Tool Mastery | Multi-Row Formula

Let me know if it answers your question.

John

[Zynk]

I tried the multi tool but it still seem to have issue. i have the following that i am using in the formula tool for now

 where [holidaycounter] is as follows:

if ([Counter]= 0) and ([Counter] != 1 or [Counter] != 2 or [Counter] != 3 or [Counter] != 4 or [Counter] != 5 or [Counter] != 6) then "W" elseif ([Counter] = 0 or [Counter] = 1) and ([Counter] != 2 or [Counter] != 3 or [Counter] != 4 or [Counter] != 5 or [Counter] != 6) then "H1" elseif ([Counter] = 0 or [Counter] = 1 or [Counter] = 2) and ([Counter] != 3 or [Counter] != 4 or [Counter] != 5 or [Counter] != 6) then "H2"
elseif ([Counter] = 0 or [Counter] = 1 or [Counter] = 2 or [Counter] = 3) and ([Counter] != 4 or [Counter] != 5 or [Counter] != 6) then "H3" elseif ([Counter] = 0 or [Counter] = 1 or [Counter] = 2 or [Counter] = 3 or [Counter] = 4) and ([Counter] != 5 or [Counter] != 6) then "H4"
elseif ([Counter] = 0 or [Counter] = 1 or [Counter] = 2 or [Counter] = 3 or [Counter] = 4 or [Counter] = 5) and ([Counter] != 6) then "H5"
elseif ([Counter] = 0 or [Counter] = 1 or [Counter] = 2 or [Counter] = 3 or [Counter] = 4 or [Counter] = 5 or [Counter] = 6) then "H6"
else Null() endif

where counter= 0 represents day today counter= 1 represents tommorow and counter = 2 represents day after tommorow and so on.. What i am trying to achieve is to have today coupled with coming days and create a flag of them coupled together. What the counter part does is it matches with the holiday dates and returns the number. Lets say tommorow is holiday then the counter would show 1 . if after 4 days there is a holiday it will show 4 and so on. "W" represents workday and "H1,H2,H3 ..etc " represents holiday coupling with the work day. Once i have that formula in place i am creating another column  to group them up

IF [HolidayCounter] = "H6" and [Counter] = 6 and [SLA DAYS] = [SLA]-6 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 5 and [SLA DAYS] = [SLA]-5 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 4 and [SLA DAYS] = [SLA]-4 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 3 and [SLA DAYS] = [SLA]-3 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 2 and [SLA DAYS] = [SLA]-2 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 6
ELSEIF [HolidayCounter] = "H6" and [Counter] = 0 and [SLA DAYS] = [SLA]then 6
ELSEIF [HolidayCounter] = "H5" and [Counter] = 5 and [SLA DAYS] = [SLA]-5 then 5
ELSEIF [HolidayCounter] = "H5" and [Counter] = 4 and [SLA DAYS] = [SLA]-4 then 5
ELSEIF [HolidayCounter] = "H5" and [Counter] = 3 and [SLA DAYS] = [SLA]-3 then 5
ELSEIF [HolidayCounter] = "H5" and [Counter] = 2 and [SLA DAYS] = [SLA]-2 then 5
ELSEIF [HolidayCounter] = "H5" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 5
ELSEIF [HolidayCounter] = "H5" and [Counter] = 0 and [SLA DAYS] = [SLA] then 5
ELSEIF [HolidayCounter] = "H4" and [Counter] = 4 and [SLA DAYS] = [SLA]-4 then 4
ELSEIF [HolidayCounter] = "H4" and [Counter] = 3 and [SLA DAYS] = [SLA]-3 then 4
ELSEIF [HolidayCounter] = "H4" and [Counter] = 2 and [SLA DAYS] = [SLA]-2 then 4
ELSEIF [HolidayCounter] = "H4" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 4
ELSEIF [HolidayCounter] = "H4" and [Counter] = 0 and [SLA DAYS] = [SLA] then 4
ELSEIF [HolidayCounter] = "H3" and [Counter] = 3 and [SLA DAYS] = [SLA]-3 then 3
ELSEIF [HolidayCounter] = "H3" and [Counter] = 2 and [SLA DAYS] = [SLA]-2 then 3
ELSEIF [HolidayCounter] = "H3" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 3
ELSEIF [HolidayCounter] = "H3" and [Counter] = 0 and [SLA DAYS] = [SLA] then 3
ELSEIF [HolidayCounter] = "H2" and [Counter] = 2 and [SLA DAYS] = [SLA]-2 then 2
ELSEIF [HolidayCounter] = "H2" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 2
ELSEIF [HolidayCounter] = "H2" and [Counter] = 0 and [SLA DAYS] = [SLA] then 2
ELSEIF [HolidayCounter] = "H1" and [Counter] = 1 and [SLA DAYS] = [SLA]-1 then 1
ELSEIF [HolidayCounter] = "H1" and [Counter] = 0 and [SLA DAYS] = [SLA] then 1
ELSEIF [HolidayCounter] = "W" and [Counter] = 0 and [SLA DAYS] = [SLA] then 0
Else
Null()
Endif

here sla days represent the current days past the item and sla represents the threshold for the item. I cant get the above to work as intended though perhaps you can tell me what am i missing here ?

[danrh]

Something like this should do the trick:

This takes your list of dates with their respective tasks, identifies holidays (you'll need to feed these in through a separate data source), uses a Multi-Row Formula to copy down the date field across weekends and holidays, and finally Summarizes the tasks to each day they are to be performed.

Take a look, see if it answers some questions.

[Zynk]

The Solution looks good although regarding holidays i need to output them too. My goal is not to discard them. Lets say there are two holidays after today. So i need to send items for today including items that will expire on the coming two days in advance. Hence i was using a counter to assign tags to each holidays and output the item expiration date 2 days in advance. indication as lets say 10 day is the time allotted for an item. so if on friday i send the item . i will have to send items that are expiring on that day. Lets say 10th day of the item. then 9th day items (expiring on saturday) and 8th day items (expiring on sunday). With weekends its easy to achieve and i have a working model for weekends. It gets trickier when i am working with holidays lets say today is monday and  tommorow is a public holiday. So i need the app to onlu calculate 10th day items and 9th day items. I dont need 8th day items then . Similarly this year there are gonna be 5 days holidays for xmas. So before holidays i would need items for 10th,9th,8th,7th,6th,5th etc. certainly the 10 is a variable which varies for different items hence denoted by the field SLA. So this solution will work if i have just sat and sun to worry about or need to discard the holiday items. On the contrary i need to have those holiday items (just in advance).

[danrh]

Apologies, I had an error in the Multi-Row Formula. See below and the new attachment:

Was: 

IF DateTimeFormat([YourDate],'%A') IN ('Saturday','Sunday')
THEN [Row-1:YourDate]
ELSEIF !IsNull([Row-1:Holidays])
THEN [Row-1:YourDate]
ELSE [YourDate] ENDIF

Should be:

IF DateTimeFormat([YourDate],'%A') IN ('Saturday','Sunday')
THEN [Row-1:YourDate]
ELSEIF !IsNull([Holidays])
THEN [Row-1:YourDate]
ELSE [YourDate] ENDIF

If this still isn't doing what you need, can you post some sample data and your expected output?

[Zynk]

I am attaching some dummy information to give an example as to what i speak of.. The data i need to review is dummy data. SLA threshold is the SLA by when the item needs to be completed. In  the dummy data there is a field called "SLA Days" that represents the current SLA day of the item. Now the solution i speak of is somewhat where the sla threshold matches the IG in dummy data and checks for their sla days , matching it with the sla threshold. The macro for holidays is attached in the file as well. So for example today is monday. Tommorow and day after tommorow (i.e tue & wed) are public holidays. I would want the tool to output the items that would reach their sla threshold on tue and wed, when i am sending the items on monday. hence i would need to send items for mon (SLA threshold) tuesday (SLA theshold-1) and wednesday (SLA threshold -2) on monday. The tool needs to calculate automatically for the upcoming holidays. I have a working tool for calculating for sat and sun but i need something that can be more dynamic with changing holidays. Hence i was dabbling with the idea of counter. something like the attached pic.

[danrh]

I'm having trouble opening your data file.  Could you copy and paste a sample into an excel file and re-upload?

[Zynk · Accepted solution]

Thank you for the help, I went along with using using progressive counter to get the job done. Certainly it is not a clean solution but i have to use iterative method in order to check. I used a macro to separate all working days in a year and all holidays including sat and Sunday. Then used the join tool to create counters for each day. If the day today is not a holiday it will give it a value 0. If tomorrow is a holiday it will give 1 if tomorrow and day after tomorrow is a holiday it will update the value to 2 and so on. the counter stops if tomorrow is not a holiday, as well if after 1 day is not a holiday. That way i am able to separate by days the item and do the necessary calculations based on the days ahead.