Weekends and Holiday

[amyceres]

I know that this formula been touch on over and over again but I need help and solving this glitch in my formula There is three parts to this problem This is what I have currently..
For the @ Holidays
I have this formula below however, when I check the formula for errors it keep saying that
"The ) is missing." How many of these are missing?????

BeforeReadingRecords;
DateVar Array Holidays := [
Date (2013,1,1),
Date (2013,1,21),
Date (2013,2,18),
Date (2013,5,27),
Date (2013,7,4),
Date (2013,9,2),
Date (2013,10,14),
Date (2013,11,11),
Date (2013,11,28,
Date (2013,12,25),
Date (2013,12,31),
Date (2014,1,1),
Date (2014,1,20),
Date (2014,2,17),
Date (2014,5,26),
Date (2014,7,4),
Date (2014,9,1),
Date (2014,10,13),
Date (2014,11,11),
Date (2014,11,27),
Date (2014,12,25),
Date (2014,12,31),
Date (2015,1,1),
Date (2015,1,19),
Date (2015,2,16),
Date (2015,5,25),
Date (2015,7,4),
Date (2015,9,7),
Date (2015,10,12),
Date (2015,11,11),
Date (2015,11,26),
Date (2015,12,25),
Date (2015,12,31),
Date (2016,1,1),
Date (2016,1,18),
Date (2016,2,15),
Date (2016,5,30),
Date (2016,7,4),
Date (2016,9,5),
Date (2016,10,10),
Date (2016,11,11),
Date (2016,11,24),
Date (2016,12,25),
Date (2016,12,31)
];
0

So I put the ")" in and the message so that I received was "Too many arguments have been given to this function."

Date (2016,11,24),
Date (2016,12,25),
Date (2016,12,31)
)];
0

Another thing is that I know that this formula count the numbers of days between the business days...so if current day is 4/2/13 the number of days from between current days and last application day which is 4/1/13 is 2 days including current date. and I was wondering how it would it be possible to show it in date format for the number of business days ( Which would count the current date as 1 day)

WhilePrintingRecords;
Local DateVar Start := {lt_master.application-dt};
Local DateVar End := currentdate;
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;

Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

Because for the appliciation dates I only want to populate dates from current - 4 business dates.
So if was today was 4/3/13 I only wants to pull up applications date on 3/29/13
So it would be if today was 4/4/13 I only want to pull up dates on 4/1/13
etc etc....

if currentdate= true then {lt_master.application-dt}-{@Weekends)

Help is much appreciated....

 

[pmax9999]

The first problem is that the 9th date in the list is missing the closing bracket - "Date (2013,11,28". Fix that and the first problem goes away.

Don't have time to look at the other issues, but will do so later if nobody else is able to help in the meantime.

Cheers
Pete

 

[amyceres]

I have most of the stuff solved for this problem however, now that I don't count the weekend, when Friday roll around the weekend days are included in the report now.
and I only want the weekday reports excluding the Holiday.

Today I pulled up the reports for currentday.
4/5/13

I only want {lt_master.application-dt}=4/1/13 which is Monday so I set that

@Weekends =5 but beside getting the 4/1/13 I also get 3/30/13 and 3/31/13 since it is also considered 5 days which I don't want...I trying to run this report automatically. So that I don't have to go in every Friday to fix it.

thank you

 

[pmax9999]

Is it possible there would be data for weekends and public holidays that would need to be ignored? Would there be data for every weekday that wasn't a public holiday?

Pete

 

[amyceres]

Yes, there would be data for weekends and public holidays that would need to be ignored and I would only need data for Monday- Fridays unless there is a holiday any of those days. Short of listing all 104 dates for the weekends to ignored what is an easier way?
Thank you

 

[amyceres]

? Anybody?

 

[briangriffin]

I'm sorry I don't have time to go into a lot of detail, but you can save yourself many, many hours of heartache if you can create a date table on your database server (it doesn't have to be in the same database as your data, and can even be on a different server as long at it can be queried). It makes writing any date based formula a breeze, and it's always the first thing I do when starting work for a new client.

If that's a possibility in your environment then search Tek-Tips for some more info, or post here and I'll give you some examples.

 

[amyceres]

Please give me information? or examples?

 

[briangriffin]

What is your database platform?

 

[amyceres]

All the information I pulled is from CVS file.

 

[briangriffin]

When you create a new report, aren't you connecting to a database server of some type - SQL Server, Oracle, MySQL, etc.?

 

[amyceres]

It's a in house software database program that we used called Unifi.

 

[briangriffin]

It doesn't look like I'll be able to give you specifics for your environment, but in general you would create a table and populate it with every date for the next five or ten years. The date is the primary key.

Then you add additional columns to the table that describe the date - for instance, fiscal year, fiscal period, payperiod start, payperiod end, is holiday, etc. - whatever would be useful to you. It may take some time to fully populate, and you can always add additional columns, but it's worth the effort.

So if you inner join this table to your application table using the date field, and you wanted some kind of report excluding holidays, you'd just put something like this in your selection criteria:

{lt_master.application-dt} in [3/1/2013 to 3/31/2013]
and
datetable.isholiday = 'N'

The beauty is that it's available for any report, and relieves you from continually writing complex date formulas.

 

[pmax9999]

Based on my understanding of the challenge, I don't see the need for additional table. If the initial need is to return data for business days only (ie, excluding weekends and public holidays days), add the following to your Record Selection:

[Code RecordSelectionFormula]
WhileReadingRecords;
DateVar Array Holidays;

{Data.Date} >= CurrentDate - 10 and
{Data.Date} < CurrentDate and
{Data.Date} <> Holidays and
Not(WeekDay({Data.Date}) in [1, 7])
[/Code]

, where "Holidays" is the variable calculated in the formula from your first post (amended as per my initial post).

This will return every business day in the previous 10 calendar days. You could change the "10" to a smaller number, as long as it will still provide the 4 business days required, and will depend on the maximum number of public holidays you are likely to experience in any given period.

Limiting the records to just the most recent 4 is going to be much more complicated, and will depend on the structure (grouping etc) of the report. If you can live with returning more than 4 records under most circumstances but always at least 4, I would leave it at that. If it must only be 4 days, please provide more detail on the structure of the report, including some sample data.

Cheers
Pete

 

[amyceres]

Thanks Pete for the help..I am assuming it should look like this.???

WhilePrintingRecords;
Local DateVar Start := {lt_master.application-dt};
Local DateVar End := currentdate;
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

{lt_master.application-dt} >= CurrentDate - 10 and
{lt_master.application-dt} < CurrentDate and
{lt_master.application-dt} <> Holidays and
Not(WeekDay({lt_master.application-dt}) in [1, 7])

 
Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;

Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek ( Holidays[i] ) in 2 to 6 and 
     Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

Not working..

 

[pmax9999]

No, that's not what I intended. It is of course possible I just don't understand what you are trying to do though.

The code I provided was intended to be used as (or added to) the Record Selection, not into a formula.

As I said though, it will return data for all Business Days in the previous 10 calendar days, ie excluding weekends and public holidays (actually, any date that has been added to the "Holidays" array). You can reduce the number of calendar days (in the 1st line) - that number will depend on the maximum number of public holidays you are likely to encounter in close proximity. For example, here in Australia, we have 2 days at Easter and can have another another public holiday all very close together (depending on exactly when easter falls) with a weekend, meaning a possible 5 days of "non-business" days, so to be sure to get at least 4 business days I need to include at least 9 calendar days worth of data.

The last line of the code (Not(WeekDay({Data.Date}) in [1, 7])) is the part that excludes Saturdays and Sundays

Hope all this makes sense.

Cheers
Pete

 

[amyceres]

I placed the formula in the Record Selection like you said to and it works!!!Thanks! but the @Holiday formula was suppose to be inserted into the Report Header in the Design View? I don't think that would work because it shows nothing.

 

[pmax9999]

The holiday formula will not show anything. It's purpose is only to populate the "Holiday" array so the record selection formula can use the data.

Pete

 

[amyceres]

thank you for the information...I found that I probably going to include the holiday in the @weekend formula because I am not getting the result that I want from keeping everything separate.