Difference of two dates to take into account Factory Calendar

[Former Member]

Does any one know how to do this in BEx. I need to calculate the difference of two dates to get the number of 'WORKING' days by taking into account the factory calendar.

Alternatively I was going down the route of defining a custom function in the front end but I did not know where to attach me function. I have a function module that can take two dates and a ID for factory calendar and determine the number of working days. I need a way to attach it to the formula editor.

Any help is appreciated. Points will be awarded.

Thanks

Ravi

[Former Member]

Try using creating Formula, it should help.

-Harshal

[Former Member]

did u check this

it also talks about how to use key dates and their differences

u can try this similarly with different sense.....

http://www.sd-solutions.com/documents/SDS_BW_Exception_Reporting.html

Exceptions in BW reports are used to highlight specific data in your query that exceeds or falls short of a defined limit. This is displayed to the user in the form of traffic light colours. Additionally, Exceptions are also used as the basis for defining Alerts.

Use:

An example for the application of an exception is a query that analyses the absence days of employees over a 12 month period. In the example, all employees must be marked as critical (displayed as red) that have an absence rate of 30 days or more.

Those employees marked as normal (displayed as yellow) have an absence rate between 11 and 29 days. The employees with good attendance records (displayed as green) have an absence rate of less than 11 days.

How to:

• Click on the Exceptions button in the Query Designer and select the function New Exception.

• The Defining Exceptions window will appear. Enter some descriptive text in the Description and ensure the Active flag is set.

• Select the key figure you want to evaluate for the exception.

• On the Exception Values tab, click the New button and specify the threshold value and Alert Level. Use the entry fields at the bottom of the window to enter the definition for each alert level and click the Transfer button to append the definition into the Exception Values list. You must make these settings for all three alert levels.

• On the Cell Restriction tab, select the setting ‘All (Recommended with Relative Numbers).

• Complete the Exception definition by clicking the OK button.

After the definition and query are saved, run the report. You will see that each employee Absence Days key figure is coloured according to the exceptions defined. See Figure 4

In the query header you will see the Absence Exception you have just created is Active. To turn off the exception and return the key figures to their original colour, double click the Active text.

[Former Member]

Hi Ravi,

I have the same issue but this cannot be achieved from the front end this can be done using Virtual keyfigures. Below is the code I used for this

Below is the code for the include ZXRSRZZZ .

FIELD-SYMBOLS <L_Z_LOC_MAN> .

FIELD-SYMBOLS <L_0FACTCAL_ID> .

FIELD-SYMBOLS <L_0NUMDAY> .

FIELD-SYMBOLS <L_0NUMWDAY> .

ASSIGN COMPONENT g_pos_zloc_d01_z_loc_man OF STRUCTURE

C_S_DATA TO <L_Z_LOC_MAN> .

ASSIGN COMPONENT g_pos_zloc_d01_0factcal_id OF STRUCTURE

C_S_DATA TO <L_0FACTCAL_ID> .

ASSIGN COMPONENT g_pos_zloc_d01_0numday OF STRUCTURE

C_S_DATA TO <L_0NUMDAY> .

ASSIGN COMPONENT g_pos_zloc_d01_0numwday OF STRUCTURE

C_S_DATA TO <L_0NUMWDAY> .

SELECT SINGLE FACTCAL_ID INTO FAC_CAL FROM /BIC/AZLOC_D0100

WHERE /BIC/Z_LOC_MAN = <L_Z_LOC_MAN> .

IF NOT FAC_CAL IS INITIAL .

DATA : DATE1 TYPE DATUM ,

DATE2 TYPE DATUM ,

DATE3 TYPE DATUM ,

NODAYS TYPE I,

NOWDAYS TYPE I .

CLEAR: DATE1, DATE2, DATE3, NODAYS, NOWDAYS .

if DATE99 is initial .

date99 = sy-datum - 1.

endif.

CALL FUNCTION 'SLS_MISC_GET_LAST_DAY_OF_MONTH'

EXPORTING

DAY_IN = date99

IMPORTING

LAST_DAY_OF_MONTH = date2

EXCEPTIONS

DAY_IN_NOT_VALID = 1

OTHERS = 2.

concatenate date99+0(6) '01' into date1 .

do .

if date1 > date2 .

exit .

endif.

CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'

EXPORTING

DATE = date1

FACTORY_CALENDAR_ID = FAC_CAL

IMPORTING

DATE = date3

EXCEPTIONS

CALENDAR_BUFFER_NOT_LOADABLE = 1

CORRECT_OPTION_INVALID = 2

DATE_AFTER_RANGE = 3

DATE_BEFORE_RANGE = 4

DATE_INVALID = 5

FACTORY_CALENDAR_NOT_FOUND = 6

OTHERS = 7.

IF DATE3 EQ DATE1 .

NODAYS = NODAYS + 1 .

ENDIF.

DATE1 = DATE1 + 1 .

ENDDO.

concatenate date99+0(6) '01' into date1 .

DATE2 = date99 .

DO .

if date1 > date2 .

exit .

ENDIF.

CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE'

EXPORTING

DATE = date1

FACTORY_CALENDAR_ID = FAC_CAL

IMPORTING

DATE = date3

EXCEPTIONS

CALENDAR_BUFFER_NOT_LOADABLE = 1

CORRECT_OPTION_INVALID = 2

DATE_AFTER_RANGE = 3

DATE_BEFORE_RANGE = 4

DATE_INVALID = 5

FACTORY_CALENDAR_NOT_FOUND = 6

OTHERS = 7.

IF DATE3 EQ DATE1 .

NOWDAYS = NOWDAYS + 1 .

ENDIF.

DATE1 = DATE1 + 1 .

ENDDO.

<L_0FACTCAL_ID> = FAC_CAL .

<l_0NUMDAY> = NODAYS .

<l_0NUMWDAY> = NOWDAYS .

ENDIF.

Hope this helps.

PV

[Former Member]

Thanks PV for the code and solution. I will try it this way.

Ravi

[Former Member]

Follow this link.

Rather than ABAP Code we can do using Formula Variable with Replacement Path.

http://www.sd-solutions.com/documents/SDS_BW_Replacement%20Path%20Variables.html

[Former Member]

Hi Sriram ,

Thanks for your response. In your example you how the difference in Calendar days. If you look at my original request I am looking for Factory Calendar days.

Thanks

[Former Member]

Hi Ravi,

I am also facing the same problem.

Have you got any solution for this ??

Regards,

Chakri

[Former Member]

None so far. If I get some I will post.

Thanks

Ravi

[Former Member]

Hi Ravi,

Create a variable with processing type Customer exit and write a code there which uses your FM according to your requirement.This will solves the problem.

Regards

Karthik

[Former Member]

I cannot do the user exit since the two dates are Key Figures (not characteristics). Therefore I need to apply the custom functions at the row level. For e.g

Material GI Date Delivey Date Result (taking into Account Factory Calendar)

ABC 20071010 20071005 3 working days

I could try replacement path but it does not let me define a factory calendar.

Thanks

Ravi