Working days and calendar table

building reports desktopintelligence
1

Hi,

I’m so new to BO that I am failing to understand how to accomplish a fairly easy task.
I have created an Excel spreadsheet that has every day from 01/01/1980 to 31/12/2025. I have columns that show if the day is a weekend or holiday and a column with 0 or 1 to reflect this.

Browsing around I gather you can then sum your values and get the working days. I cannot, however, work out how to create the variable in BO (using 6.5). I need to use two dates, obviously, as my start and end and then link in my calendar table.

Can someone please explain how to do this before I put my foot through the monitor !!

Wish I was still using Access !!

Thanks in advance for any help.

dc8 :uk: (BOB member since 2009-07-10)

2

Hi,
If you want to make the sum of a measure object then you can just say

Sum( where ‘’ =1

This will sum up the results where there the flag value is 1 i.e. if it is working day.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

3

Hi Omkar,

Thanks for the help.

What I cannot work out is how to select which values to sum ?

I have a and date in the database which I use to find the period concerned. However, what I cannot work out is how I then , for this date range add, up the values in my calendar table to give me the result.

So what I want is (I know this is wrong but to explain)

=sum () where between (,)

All I get at the moment is 1 because the field matches the date. It does not take into account the date.

Why are such simple things so difficult (or impossible) to achieve in BO ?

C

dc8 :uk: (BOB member since 2009-07-10)

4

Hi,
I guess this is not the fault of BO but your logic or understanding.
When you give the date between specific range then data will come only for that period only.

If you want value for say working days and you have marked the value=1 for the working day then you should do =sum(measure object) where (flag=1) this will give you sum of the measure where the day was a working day. You dont have to involve the date inteh where clause of the object, it is automatically getting the data for that range only when you give it in the query.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

5

Thanks Omkar,

I guess where I am missing the point is that each record that I am looking at in my dataset will have different and dates.

So, for each record, I need to determine the number of days EXCLUDING weekends and holidays.

The only time a prompt that might be used would be to retrieve the data for each record within a particular time period.

The user would select 01-08-09 to 31-08-08 to retrieve data for the date being between these dates. Once this data is returned, as I say, each record will have different values for their individual and dates.

Hope this makes sense ?

C

dc8 :uk: (BOB member since 2009-07-10)

6

Hi,
Look, if you want to calculate something then you should just include the where clause the way i have defined. Then if you select a date range between the date range specified in the From Date and To date prompt, then teh date will be in between the promtp values. Each records, will have only one and one date.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

7

Hi Omkar,

I guess that I am totally failing to see how this should work.

Here is a line from the actual data

PNum BOOKDATE APPDATE zzztest2 Difference Days Waiting Time Waiting Time No BH
Z99999 24/12/2008 29/12/2008 2,277.00 5 3 1

As you can see, the value returned in is WRONG !! Its the total of all the entries in the spreadsheet that are not flagged as either a weekend or a holiday.

As I said earlier, how does the system work out that it should be looking at the and fields and ONLY summing for these dates !!

The actual values are shown in the last 3 columns

5 days between the two dates, 3 days with weekends removed and 1 day with holidays removed. Just a slight difference to the value BO is bringing back !!

I have been using databases for more years than I can remember and can only say that BO does not seem capable of doing some very simplistic tasks like this without causing major headaches. Wish I’d never taken the job :wink:

C

dc8 :uk: (BOB member since 2009-07-10)

8

Hi,
First of all i still dont think BO is not capable, you are not capable of linking what you want and what you have.

It will be good if you share a part of your spread sheet instead of just shaing one line then it will be east to understand the spread sheet.

Ideal scenario where the spreadsheet will have the date, day name and the value whether a working day or not will sove the issue.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

9

Hi Omkar,

Although I only included one line all the data will be the same format from the dataset I have. An ID, BOOKDATE and APPDATE. There are many other columns but these are irrelevant to this issue.

The columns I have in the Excel spreadsheet are as follows

DATEVALUE=Date dd/mm/yyyy
COUNTFLAG=1 or 0 to allow SUM
BH=Y or N to indicate a holiday
WEEKEND=Y or N to indicate a weekend
INCLUDE=Y or N to indicate whether it should be included in the SUM

If I don’t link the DATEVALUE to either the BOOKDATE or the APPDATE the SUM will return over 9500 records. If I link the DATEVALUE to EITHER BOOKDATE or APPDATE that is where I get the 2277 value.

Hope this explains a little more about the data.

My main problem still is - how does BO know what dates to include for the SUM for each individual record, and, how is this achieved ?

Can it be done via a formula or does another approach have to taken ?

Thanks for your patience, although mine is nearly at an end with BO in general. I still think its a horrible piece of software !!

C

dc8 :uk: (BOB member since 2009-07-10)

10

Hi,
If you just have one column whether it is a working day or not. then this should suffice.
In my original email i wrote the formula, it just sums up the values which has valus for the flag as 1.
Say you set value of 1 for the working day then if you write sum where flag value is 1, then it will just sum up the values which have flag value as 1.

I hope this clears the doubt.

Omkar Paranjpe :us: (BOB member since 2006-02-13)

11

Hi Omkar,

Thanks for taking the time to reply.

However there obviously seems to be something fundamental that I am not grasping here in order to allow me to get this to work.

As I continue to state - how does BO know what dates to start and end the SUM function for each record ?

I have used your formula (and tried a million other variations) but will ALWAYS get the values I referred to in an earlier post. Over 9500 if I don’t link the PDF and the existing data and 2277 if I do.

There is NO calculation being done anywhere, for each record, that says “start at this date, include all the dates up to and including the end date, THEN sum ALL these days to obtain the result”

What BO needs to do is this

24/12/2008 BOOKDATE
25/12/2008 does not exist in the MAIN dataset, somehow calculate this
26/12/2008 does not exist in the MAIN dataset, somehow calculate this
27/12/2008 APPDATE

Retrieve the values from the calendar for each of these dates
1
0
0
1
SUM=2

The current report uses at least 14 variables to accomplish this and is a complete mess. Also, 8 of the variables are specific to a year (those holding the holiday dates) so, every year, you need to change the dates in these variables to the current ones. If you then run the report for a different year it will be wrong because it now has the wrong dates in the variables !!

This is one of the Holiday variables

= If DaysBetween( , ‘25/12/2008’) <0 Then 0 Else If DaysBetween( ,) >DaysBetween( ,‘25/12/08’) Then 1 Else 0

There are 7 more of these which need maintaining each year, or, of course, you could always save a copy of the report specific to that year so that you can run on current or old data. Not very efficient and prone to people using the wrong report and getting the wrong results.

BO sucks big time I’m afraid !!

C

dc8 :uk: (BOB member since 2009-07-10)

12

Hi,
I explained what we implemented at our side in the calendar able, we had one flag which sayswhether the day is a working day or not.

Say a day is sunday so against that in the table, the value will be marked as 0 and if it is a working day then it will be 1.

So if you have recrods like

<date 1> Sunday $10000
<date 2 > Tuesday $2000
<date 3> Friday $500

So when you says sum when day is a working day, here it will sum up for Tiesday and friday and not the sunday value. So the total sum will be $2500.

Then this fact table will be joined to the calendar table on teh basis of the date.

So essentially it will fetch all the records in the given time range, but will sum up the values only for which te flag in the calendar table will have value as 1.
In this case BO dont need to know which date to take or not, it will decide on basis of the flag. And if you have used the date range flter in the results set then it will have that many records only.

This is all we had implemented and it is working fine with us for years.
If you want me to do something other than posting on this forum, then please pay me the consulting fees :wink:

Omkar Paranjpe :us: (BOB member since 2006-02-13)

13

Don’t get me started LOL :mrgreen:

Have you seen this:-

https://bobj-board.org/t/104238

That will give you working days, but not public holidays - Desktop solution.

To be honest this is a very difficult / impossible thing to do in SQL.

What we did was we used quite complex SQL in an object, that did the number of working days calc between the two dates, then subtracted a DB level function that tested between those same dates for number of public holidays.

This is the SQL for the number of working days between minus the holidays function:-

Case When Datepart( dw,dbo.Start.StartDate) = 7 Then
    Case When Datepart( dw, dbo.End.EndDate) = 7 Then 
             Case When
                 (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) +(Datepart( dw,dbo.End.EndDate)-2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)< 0 
                       Then 0
              Else
                   (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate)-2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
               End
     Else
          Case When
                    (7 - Datepart( dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) < 0 
           Then 0 
           Else
               (7 - Datepart(dw, dbo.Start.StartDate) ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
          End   
    End
Else
     Case When Datepart(dw, dbo.End.EndDate) = 7 
          Then
          Case When (7 - Datepart( dw, dbo.Start.StartDate)-1 ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw, dbo.End.EndDate) - 2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)<0 
                      Then 0 
                         Else (7 - Datepart( dw, dbo.Start.StartDate)-1 ) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw, dbo.End.EndDate) - 2)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
                          End
       Else
       Case When
                 (7 - Datepart(dw, dbo.Start.StartDate) -1) + ((Datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart( dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate)<0 
                 Then 0 
                     Else (7 - datepart(dw, dbo.Start.StartDate) -1) + ((datediff( week, dbo.Start.StartDate, dbo.End.EndDate) - 1) * 5 ) + (Datepart(dw,dbo.End.EndDate) - 1)-dbo.GetHolidayDays(dbo.Start.StartDate,dbo.End.EndDate) 
                  End 
        End
End

dbo.GetHolidayDays is the subtracted function, unfortunately I have lost that somewhere, ask your DBA for help!

Mak 1 :uk: (BOB member since 2005-01-06)

14

Well I guess we’ll just have to put this one away without a result.

I can see exactly how the example you give will work BUT this is based only on one date in your table. I have two dates which, as I have asked many times about, is where I have the problem.

I need the system to look at the start date (1 field) and the end date (2nd field)

Thanks for trying to help anyway.

C

dc8 :uk: (BOB member since 2009-07-10)

15

Read the SQL, it uses Start Dates and End Dates, it compares two completely different date columns dbo.Start.StartDate and dbo.End.EndDate, that were attached to two seperate calendar tables!

You have to test some individually to work out whether they are falling at weekends. I know this works as I tested it endlessly!

Try it against your database, you will see…:slight_smile:

Also, as far as I can see, the Deski example I have linked to does the same.

Mak 1 :uk: (BOB member since 2005-01-06)

16

Hi Mak 1

Thanks for your code.

My reply was about giving up must have “crossed in the post” as it was meant to finish off the discussion I was having with Omkar.

I’ll have a look at your message later and see how I get on with the contents.

Cheers again,

C

dc8 :uk: (BOB member since 2009-07-10)

17

No problem :).

It is a difficult thing to do, I know, we spent quite a while on it :twisted:.

The SQL without the function will only give you the working days.

The DB holiday function is actually very simple, I just can’t find it for you at the mo!

Mak 1 :uk: (BOB member since 2005-01-06)

18

Thanks Mak 1,

I’ll talk to the database guys next week and sort out getting the calendar added to the Progress database and the universe.

Hopefully once its there I’ll be able to work out what to do.

Still think BO is c@@p though !!

C

dc8 :uk: (BOB member since 2009-07-10)

19

LOL, well it would be good if it had a built in working day function like Excel!

Infact you can write your own Deski functions if you could work out how to do this one let me know!!

Its a very common business question…:).

Mak 1 :uk: (BOB member since 2005-01-06)

20

BO is fine. Access is pants. You’ll come round eventually. :stuck_out_tongue: