Calculating MTD, QTD and YTD in BW Transformation

[BenedictV]

If you are a BW developer and you just read the title, you are probably thinking ‘…but shouldn’t that be done in the query’. You are right the ‘to date’ variants for aggregating date should be done at the query level whenever possible, which is almost all the time. But there do arise, situations when you want it done in the back-end transformations.

                I came up with one such scenario recently where we had to do the MTD, QTD and YTD calculations in our transformation layer. We had to calculate the ‘to-date’ aggregates for our key figures and store the data in the back-end. I couldn’t find a lot of information on how people usually do this when they have to do this in the back-end.

                The first thought that came to mind is to ‘loop the loop’, wherein we loop through the end-routine data and calculate the aggregation in an inner loop for every record. Say,  I have one year’s worth of data for a given account number, I would take each record and run it in a loop and decrement the date each time until the first day of the year aggregating my key figures each time. For example, if I had a record for 20151231, I would run the loop 365 times adding up my key figures until 20150101 calculating MTD, QTD and YTD inside the loop. But we all know a loop inside a loop is a strict No-no.

                I was experimenting on different ways to do this without involving a too much looping around. One such method is to use the AT-NEW ‘control level statement’, (https://help.sap.com/saphelp_470/helpdata/en/fc/eb381a358411d1829f0000e829fbfe/content.htm). The control-level statements itself have been in existence for a long time, but I am sure not many of us BW developers would have considered it inside of transformations.

My source metadata looked something like this,

I was calculating the ‘to-date’ values at the lowest level of granularity, involving all the key fields.

In the end routine do a look back on source and SELECT-FOR ALL ENTRIES in result package at FISCYEAR level. This will give you a full year’s data even if a record exists for single date in a year in the result package.

SELECT  CO_AREA COMP_CODE PROFIT_CTR COSTCENTER CURTYPE FISCVARNT
CHRT_ACCTS ACCOUNT FISCYEAR CALQUARTER FISCPER DATE0
AMOUNT CURRENCY
FROM /BIC/AZTDSO00100
INTO CORRESPONDING FIELDS OF TABLE lt_ZTDSO001
FOR ALL ENTRIES IN RESULT_PACKAGE
WHERE CO_AREA     = RESULT_PACKAGE-CO_AREA
AND   COMP_CODE   = RESULT_PACKAGE-COMP_CODE
AND   PROFIT_CTR  = RESULT_PACKAGE-PROFIT_CTR
AND   COSTCENTER  = RESULT_PACKAGE-COSTCENTER
AND   CURTYPE     = RESULT_PACKAGE-CURTYPE
AND   FISCVARNT   = RESULT_PACKAGE-FISCVARNT
AND   CHRT_ACCTS  = RESULT_PACKAGE-CHRT_ACCTS
AND   ACCOUNT     = RESULT_PACKAGE-ACCOUNT
AND   FISCYEAR    = RESULT_PACKAGE-FISCYEAR.

SORT lt_ZTDSO001 ASCENDING BY
CO_AREA COMP_CODE PROFIT_CTR COSTCENTER CURTYPE FISCVARNT
CHRT_ACCTS ACCOUNT FISCYEAR CALQUARTER FISCPER DATE0.

The order of fields in the internal table is the key here as any change of value to the left of the field for which we check ‘AT-NEW’ would trigger a change. I am doing it at the ‘ACCOUNT’ level, so any change to the right of the ACCOUNT field would register as a NEW record. . The ascending order of sort will help run the loop only once.

In the LOOP below, the AMOUNT value is aggregated over every iteration and for every true ‘AT-NEW’ the corresponding ‘to-date’ key figure value is reset,

LOOP AT lt_ZTDSO001 ASSIGNING <fs_ZTDSO001>.
AT NEW FISCPER.
lv_kf_MTD = 0.
ENDAT.
AT NEW CALQUARTER.
lv_kf_QTD = 0.
ENDAT.
AT NEW FISCYEAR.
lv_kf_MTD = lv_kf_QTD = lv_kf_YTD = 0.
ENDAT.
AT NEW ACCOUNT.
lv_kf_MTD = lv_kf_QTD = lv_kf_YTD = 0.
ENDAT.

lv_kf_MTD = lv_kf_MTD + <fs_ZTDSO001>-AMOUNT.
<fs_ZTDSO001>-/BIC/ZTKFMTD = lv_kf_MTD.
lv_kf_QTD = lv_kf_QTD + <fs_ZTDSO001>-AMOUNT.
<fs_ZTDSO001>-/BIC/ZTKFQTD = lv_kf_QTD.
lv_kf_YTD = lv_kf_YTD + <fs_ZTDSO001>-AMOUNT.
<fs_ZTDSO001>-/BIC/ZTKFYTD = lv_kf_YTD.
ENDLOOP.

Once the MTD, QTD and YTD values are calculated in the temporary internal table a second loop over the result package is necessary to copy over the calculated values.

LOOP AT RESULT_PACKAGE ASSIGNING <fs_PACKAGE>.
READ TABLE lt_ZTDSO001 INTO wa_ZTDSO001 WITH KEY
“table key
BINARY SEARCH.
IF SY-SUBRC = 0.
“populate calculated fields
ENDIF.
ENDLOOP.

NOTE: We can even do this in a single loop if we know for sure we have an entire year’s data in one package.

The second best option to using AT-NEW would be to use a parallel cursor as mentioned in this document, http://scn.sap.com/docs/DOC-69322 . I did run a few tests between these two methods to check the number of times the loop executes and you can see how even a minimal loop using the cursor method compares to using  control-level statements.

For AT-NEW Code

Loop count is for one package of 50000 records

Parallel Cursor Code

LOOP AT RESULT_PACKAGE ASSIGNING <fs_PACKAGE>.
READ TABLE lt_ZTDSO001 ASSIGNING <fs_ZTDSO001>
WITH KEY “table key
IF SY-SUBRC = 0.
lv_SYTABIX  = SY-TABIX.
LOOP AT lt_ZTDSO001 FROM lv_SYTABIX ASSIGNING <fs_ZTDSO001>
WHERE “table key
IF <fs_ZTDSO001>-FISCYEAR = <fs_PACKAGE>-FISCYEAR.
lv_kf_YTD = lv_kf_YTD + <fs_ZTDSO001>-AMOUNT.
ENDIF.
IF <fs_ZTDSO001>-CALQUARTER = <fs_PACKAGE>-CALQUARTER.
lv_kf_QTD = lv_kf_QTD + <fs_ZTDSO001>-AMOUNT.
ENDIF.
IF <fs_ZTDSO001>-FISCPER = <fs_PACKAGE>-FISCPER.
lv_kf_MTD = lv_kf_MTD + <fs_ZTDSO001>-AMOUNT.
ENDIF.
ENDLOOP.
“populate calculated fields
lv_kf_MTD = lv_kf_QTD = lv_kf_YTD = 0.
ENDIF.
ENDLOOP.

Loop count is for one package of 50000 records

The use of AT-NEW might not work for all scenarios but you can take it into consideration when you have to do some sort of aggregation inside your transformations.

And if you have a better way of doing this, please do write about it and share the link in the comments below for the benefit of the community.