Loop checking and forecasting data for holidays

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

Batch Macro

Predictive Analysis

Behavior Analysis

Prescriptive Analytics

Accepted answers

Zynk

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.

Capture.bmp

All comments

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 ?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703