Difference between 2dates excluding Week ends in a Query

[former_member671571]

Hi All,

I have 2 dates as Ristricted Key Figures. I want to find the difference between 2 dates that should exclude week ends (Saturday and Sunday). How to achieve this in Query. Any help will be appreciated.

Thanks,

Ram

[Former Member · Accepted solution]

Hi Ram,

You need to create a Customer-exit variable...

logic:

totaldays = todate - fromdate.

use function module DAY_IN_WEEK to get days of the dates like monday(01)...sunday(07). by using from date and todate one at a time..here you will get today and fromday numbers...

step 1)

So if fromday is monday u will get 01 , teusday u will get 02..Likethat..then remove totaldays - (05) for monday , totaldays - (04) for tuesday ...Likethat..

step 2)

and if today is monday then remove step1totaldays - (01) and tuesday then step1totaldays- (02)...likethat

step 3)

now weekends = step2totaldays / 7

step 4)

step2totaldays - (2 * weekends)

step 5)

Now howmany you have subtracted in step 1 and step 2

add to step2totaldays

Result will be the difference...

Try this out..If it is really useful don't forget to assign points..so that helpers can help better...

If not post your reply..we will try to solve...

[former_member671571 · Accepted solution]

Hi,

The logic seems to be pretty good. But how do I get the Ristricted Key figures in Customer Exit (todate and fromdate) for calculations. If any sample code of logic provided it will be great help. I am considering to exclude Week ends (saturday and sunday)only.

Thanks in Advance.

Ram

[former_member183777]

I had done some thing similar but probably not very exact. I just created another Calculated Key Figure using the following:

( 'Valid To' - 'Valid From' + 1 ) DIV 7 * 5 + ( 'Valid To' - 'Valid From' + 1 ) MOD 7 )

It calculates number of weeks, multiplies by 5 and assumes that remaining days are work days. Valid to and Valid From are the 2 days.

[Former Member]

Hi Ram,

I think you are restricting them by using variables right??..Then these variables will appear in selection screen..again create an Customer exit variable which will get data from those two variables and caluculate the no of days...

If useful assign points...Further queries always welcome...

[former_member671571]

Hi,

These variables are formula variables created with processing type Replacement path and restricted based on status. How do I get these Risticted key figures in Customer exit for calculations. any help appreciated.

Thanks,

Ram

[former_member184494]

Ram,

DO you want to exclude onyl Saturdays and Sundays or you need to exclude holidays as well ? in that case you will need a holiday calendar and there are separate functions for the same.

Arun