Introduction:

In a normal scenario, we may get the requirement to show and compare the data from last month or last year using the given date or system date in the query. But if we have to calculate the date using given date and day is a bit confusing. We need this scenario when displaying any sales data. If we would like to check the sales in the last month same date and that date falls on Sunday(considering outlet is closed) we dont get any data. In order to avoid this, we need to calculate the date in last month using the date and day, so that we can compare Sales of the day in last month

Normal Scenario:

Characteristic Calender Day
Key figure 15.09.2014 15.08.2014(Same date last month) 15.09.2013(Same Date Last year)

Our Scenario:

Characteristic Calender Day
Key figure 15.09.2014 12.08.2014(Same date last month) 09.09.2013(Same Day Last year)

Note: To progress with this scenario we have taken that last month will be calculated with a difference of 30 days.

Scenario:


Firstly in Query designer, we have to restrict the Calender Day with the variable for Given date – YDAT_DAY_MAN_RFI . We will be using this date to derive the LMSD & LYSD dates


We have to create to two Characteristic Variables with Customer Exit.


  1. For Last month same day – YDAT_CE_LMSD_RFI
  2. For last year same day – YDAT_CE_LYSD_RFI

16-Sep-14 3-53-12 PM.png

Write below code in CMOD

*************************************************************

WHEN ‘YDAT_CE_LMSD_RFI’.

IF i_step = 2.
clear: loc_var_range,lv_dat1,lv_dat2.
LOOP AT i_t_var_range INTO loc_var_range  WHERE vnam = ‘YDAT_DAY_MAN_RFI’.
lv_dat1
= loc_var_rangelow.
ENDLOOP.
CALL FUNCTION ‘DATE_COMPUTE_DAY’
EXPORTING
DATE = lv_dat1
IMPORTING
DAY 
= lv_dayno.

* lv_dat2 = lv_dat1 – 30.


CALL FUNCTION ‘DATE_CREATE’
   EXPORTING
*   ANZAHL_JAHRE         = 0
*   ANZAHL_KALTAGE       = 0
ANZAHL_MONATE        =  -1
*   ANZAHL_TAGE          = 0
     datum_ein           
lv_dat1
*   DATUM_EIN_ULT        = ‘ ‘
*   ULTIMO_SETZEN        = ‘ ‘
* IMPORTING
DATUM_AUS            =
lv_dat2.
*   E_TT                 =
*   E_ULTKZ              =


CALL FUNCTION ‘DATE_COMPUTE_DAY’
EXPORTING
DATE = lv_dat2
IMPORTING
DAY 
= lv_daynolm.

IF lv_daynolm > lv_dayno.
lv_dat2
= lv_dat2 – ( lv_daynolm lv_dayno  ).
ELSEIF lv_daynolm < lv_dayno.
lv_dat2
= lv_dat2 + ( lv_dayno lv_daynolm ).
ENDIF.

clear l_s_range.

l_s_range–low = lv_dat2 .
l_s_range
-sign = ‘I’.
l_s_range
opt = ‘EQ’.
APPEND l_s_range TO e_t_range.
ENDIF.

WHEN ‘YDAT_CE_LYSD_RFI’.
IF i_step = 2.
clear: loc_var_range,lv_dat1,lv_dat2.
LOOP AT i_t_var_range INTO loc_var_range  WHERE vnam = ‘YDAT_DAY_MAN_RFI’.
lv_dat1
= loc_var_rangelow.
endloop.
CALL FUNCTION ‘DATE_COMPUTE_DAY’
EXPORTING
DATE = lv_dat1
IMPORTING
DAY 
= lv_dayno.

*lv_dat2 = lv_dat1 – 365.

CALL FUNCTION ‘DATE_CREATE’
   EXPORTING
    ANZAHL_JAHRE         =  -1
*   ANZAHL_KALTAGE       = 0
* ANZAHL_MONATE        =  0
*   ANZAHL_TAGE          = 0
     datum_ein           
lv_dat1
*   DATUM_EIN_ULT        = ‘ ‘
*   ULTIMO_SETZEN        = ‘ ‘
* IMPORTING
DATUM_AUS            =
lv_dat2.
*   E_TT                 =
*   E_ULTKZ              =


CALL FUNCTION ‘DATE_COMPUTE_DAY’
EXPORTING
DATE = lv_dat2
IMPORTING
DAY 
= lv_daynolm.

IF lv_daynolm > lv_dayno.
lv_dat2
= lv_dat2 – ( lv_daynolm lv_dayno  ).
ELSEIF lv_daynolm < lv_dayno.
lv_dat2
= lv_dat2 + ( lv_dayno lv_daynolm ).
ENDIF.

clear l_s_range.

l_s_range–low = lv_dat2 .
l_s_range
-sign = ‘I’.
l_s_range
opt = ‘EQ’.
APPEND l_s_range TO e_t_range.

ENDIF.


**********************************


The report output will be as shown below.

16-Sep-14 3-53-41 PM.png


Hope this is helpful.


Thanks

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Anshu Lilhori

    I find the document little confusing.My observations:

    1. The screenshot of variable (Technical name) which you shared,cant be seen in the code.
    2. I am assuming that this YDAT_DAY_MAN_RFI must be getting filled with the current system date but it is nowhere mentioned.
    3. You have hard coded the values to get last year date which i think will not give the proper date in case of leap year.Instead you could have used FM DATE_CREATE.

    Regards,

    AL

    (0) 
    1. Venkata Naresh Kumar Chittuluru Post author

      Thanks Anshu,

      I have corrected the document and updated to clear off the clouds.

      For point 3, yes if we use the above function module we can achieve the last year date. But in our case it was okay for hardcoding to 365 days.

      (0) 
  2. Yasemin ULUTURK

    Hi Venkata,

    I think you should better change the function in the document as Anshu has mentioned. Because it can lead wrong results (every month is not 30 days or every year is not 365 days, if we would want that we could simply use offset value on the entered date variable.) . You can also use SEPA_MANDATE_ADD_MONTH_TO_DATE function giving -1 for previous month and -12 for previous year.

    (0) 

Leave a Reply