Skip to Content

How to calculate No of days based on report execution date

Hi all,

If you have any idea to achieve the below requirements.

we have two date key figures. 1. Notification created date 2. Notification completed date.

We need to find No of days.

Logic

No of Days = (Notification Completed Date) - (Notification created Date )

If notification completed date is blank.

No of Days = Current Date - Notification Date.

How we can achieve this in BEX Query with customer exit ? If possible give the code

No of days will calculate based on report execution date.

Thanks in Advance.


Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 12, 2018 at 06:27 AM

    Hi,

    I have worked on similar kind of requirement recently , please try below solution and let me know if this helps:

    Assumption : Notification End date will always be either greater than or equal to Notification created date.

    Solution : Create 1 more column in your report to populate system date in it. You can use formula variable processed by customer exit here.

    Then in the column, number of days, use below bex formula:

    ( (Notification End date – Notification start date) > = 0 ) * (Notification End date – Notification start date) + (System date – Notification start date )

    Code to populate system date:

    **There is a small catch in my code, it always return the working day. so if in your case report is executed on weekends/holiday your calculation may go wrong. Add small validation in the below code to handle weekend/holiday scenario.

    DATA:

    lv_nday TYPE I,

    lv_odate TYPE sy-datum,

    lv_date TYPE scal-date.

    lv_odate = '00000101'.

    lv_date = SY-DATUM.

    CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE

    EXPORTING
    correct_option = '+

    date = lv_date
    factory_calendar_id = <Use your calendar variant>

    IMPORTING
    factorydate = lv_fact

    lv_nday = lv_out - lv_odate.

    ls_range-opt = 'EQ'.
    ls_range-sign = 'I'.
    ls_range-low = lv_nday.
    APPEND ls_range TO et_var_range.CLEAR ls_range.

    Vote if you find it useful.

    Thanks

    Akash

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2018 at 06:31 AM

    corrected code:

    DATA:

    lv_nday TYPE I,

    lv_odate TYPE sy-datum,

    lv_date TYPE scal-date.

    lv_odate = '00000101'.

    lv_date = SY-DATUM.

    CALL FUNCTION 'DATE_CONVERT_TO_FACTORYDATE

    'EXPORTING
    correct_option = '+

    'date = lv_date
    factory_calendar_id = <use your variant>

    IMPORTING

    date = lv_out
    factorydate = lv_fact

    lv_nday = lv_out - lv_odate.

    ls_range-opt = 'EQ'.
    ls_range-sign = 'I'.
    ls_range-low = lv_nday.


    APPEND ls_range TO et_var_range.CLEAR ls_range.

    Also in Report, In column number of days use formula : date(formula variable name).

    Add comment
    10|10000 characters needed characters exceeded