Skip to Content
Author's profile photo Benedict Venmani Felix

Calculating MTD, QTD and YTD in BW Transformation

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,

SourceDataset.png

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_PACKAGECO_AREA
AND   COMP_CODE   = RESULT_PACKAGECOMP_CODE
AND   PROFIT_CTR  = RESULT_PACKAGEPROFIT_CTR
AND   COSTCENTER  = RESULT_PACKAGECOSTCENTER
AND   CURTYPE     = RESULT_PACKAGECURTYPE
AND   FISCVARNT   = RESULT_PACKAGEFISCVARNT
AND   CHRT_ACCTS  = RESULT_PACKAGECHRT_ACCTS
AND   ACCOUNT     = RESULT_PACKAGEACCOUNT
AND   FISCYEAR    = RESULT_PACKAGEFISCYEAR.

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 SYSUBRC = 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

ATNEWloopcount.png

Loop count is for one package of 50000 records

ATNEWload.png

Parallel Cursor Code

LOOP AT RESULT_PACKAGE ASSIGNING <fs_PACKAGE>.
READ TABLE lt_ZTDSO001 ASSIGNING <fs_ZTDSO001>
WITH KEY “table key
IF SYSUBRC = 0.
lv_SYTABIX 
= SYTABIX.
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
.

CursorLoop.png

Loop count is for one package of 50000 records

CursorLoad.png

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Suhas Karnik
      Suhas Karnik

      Here's an alternative:

      First, create three hashed tables - one each for for the Fiscper, Quarter and Year. So the Fiscper (or MTD) one has all your key fields + Fiscper + the Key figures but not the Quarter and Year. Similarly for the Quarter and Year. The unique key for hashing is all fields except the key figures.

      Now, do the select and fill lt_ZTDSO001. I would also remove the DATE0 field from the select and use SUM and GROUP BY in the Select statement, thereby pushing some of the calculation down to the DB. Your mileage may vary depending on whether the DB is HANA or not.

      So, the statement would be

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

      GROUP BY <<all fields except AMOUNT>>

      .

      Then,

      LOOP AT lt_ZTDSO001 INTO <fs>.

           MOVE-CORRESPONDING <fs> INTO wa_mtd.  "wa_mtd and lt_mtd do not have any time chars except FISCPER

           COLLECT wa_mtd INTO lt_mtd.   "The Fiscper-level hashed table

          

           "... and so on for QTD and YTD

      ENDLOOP.

      Finally,

      LOOP AT RESULT_PACKAGE INTO <rp>

           READ lt_mtd INTO wa_mtd WITH KEY <<your key>>

           " Fill the <rp> with the MTD value

           " Repeat above process for QTD and YTD.

      ENDLOOP.

      The Hashed table has a constant-time read unlike the Binary search which has logarithmic time.

      The SORT keyword is all too easy to use and looks harmless, but sorting a table, esp. a big one with a lot of keys, is not going to be a cheap operation. And if it is a small table, even a nested loop probably isn't going to be the end of the world.

      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix
      Blog Post Author

      Thanks much Suhas for your expert suggestion. Will try your solution. It looks less expensive than the original solution.

      I am also trying to familiarize myself with the 'GET RUN TIME FIELD' for a more accurate measurement of execution times. I will update the execution times comparing these two methods.

      Benedict

      Author's profile photo Former Member
      Former Member

      aggregation and GROUP BY will not work with FAE.

      Author's profile photo Sander van Willigen
      Sander van Willigen

      Hi Benedict,

      Cool stuff, thanks for sharing !!

      Best regards,

      Sander

      Author's profile photo Kishore Madireddy
      Kishore Madireddy

      Nice blog .. Benedict..As usual you are always rock man 🙂