Skip to Content

Let’s think over this SAP BW – BeX Query Designer issue:

image

We have an standard MM-IC infocube in BW and our customers wants to see Daily Average Stock Quantity or Value on a month based column, that is:

      DA(SQ,month)=( SQ(Day1)+SQ(Day2)+……..+SQ(LD) ) / TND(month)

      DA: Daily Average ; SQ: Stock Quantity ; LD: Last Day ; TND: Total No. of Days

For this issue if you optimistically get the “Total Stock Quantity” key figure from left, put it into the row and make its exception cumulation settings: Average and ref. characteristic: 0CALDAY, you’ll see it won’t work and you’ll see an “X” on the query output. The reason for this output is that “Stock Quantity” is an non-cumulative key figure and Average cumulation won’t be applied to it.

As a second attempt, if you grab the cumulative “Total Stock-in” and “Total Stock-out” key figures, apply the cumulative setting Average and ref char: 0CALDAY and get their sum and put them under a month column, you’ll get an daily average Stock-delta quantity for the month. But if you add this average value to the month’s beginning quantity, unfortunately what you get is not daily average stock value for the month.   

This second attempt gives us an idea. We must make some calulations with the daily stock-delta value and project this delta onto a month based daily average. Actually what we must do is to calculate the linear contribution of daily stock-delta values to the month based daily average stock value. To find this contribution, we must weight the daily stock-delta value with a MSC(month-spead coefficient). This MSC is specific to every day of a month. The formula of this MSC is:

      MSC(day) = No. of day difference to the first day of next month /  TND(month) 

      TND: Total No. of Days

      e.g. MSC(01.01.2009) = 31/31 = 1

            MSC(05.01.2009) = 26/31 = 0.8387

With these MSC’s, we weight and sum all the daily stock-delta contribution values of a month:

       Total Daily Stock-Delta Contibution = MSC(Day1)*Delta_Stock(Day1) + MSC(Day2)*Delta_Stock(Day2) + ….. + MSC(LD)*Delta_Stock(LD)

       MSC: Month-spread coeff. ; LD: Last day of month

Finally to  get the Daily Average Stock Value on month basis: 

       Daily Average Stock Value(month) = Total Stock Value (LD_PM) + TDS_DC

      LD_PM: Last Day of Previous Month

      TDS_DC: Total Daily Stock-Delta Contibution(see above)

How we will implement these formulas on BeX Query Designer is follows:

First we must define these formula variables:

ZFVAR_CALDAY : day number formula var. using 0CALDAY 

ZFVAR_MONTH : month number formula var. using 0CALMONTH2

ZFVAR_CALYEAR : year number formula var. using 0CALYEAR

We must define these Calculated Key Figures:

image

ZCKF_MONTH_DAYS :  Number of days in month (long if expression using ZFVAR_MONTH and ZFVAR_CALYEAR, calculation after cumulation flag is set off)

image

ZCKF_MONTH_DAYS_P1 : Number of days in month + 1 (Referring to the first day of next month, long if expression using ZFVAR_MONTH and ZFVAR_CALYEAR, calculation after cumulation flag is set off)

image

ZCKF_DATE_VALUE : Number of day in month (ZFVAR_CALDAY MOD 100, calculation after cumulation flag is set off)

image

ZCKF_DAY_SPREAD : ZCKF_MONTH_DAYS_P1 – ZCKF_DATE_VALUE (Difference of a day from the first day of next month)

image

ZCKF_W_STCK_QTY (weighted stok-delta quantity): ZCKF_DAY_SPREAD * Stock-delta quantity(Stock-in quantity – Stock-out quantity)

ZCKF_W_STCK_VAL (weighted stok-delta value): ZCKF_DAY_SPREAD * Stock-delta value(Stock-in value – Stock-out value)

image

ZCKF_A_STCK_QTY (daily avg. stok-delta quantity) : ZCKF_W_STCK_QTY / ZCKF_MONTH_DAYS (Cumulation tab: Exception cumulation: SUM, Ref char:0CALDAY)

ZCKF_A_STCK_VAL (daily avg. stok-delta value) : ZCKF_W_STCK_VAL / ZCKF_MONTH_DAYS (Cumulation tab: Exception cumulation: SUM, Ref char:0CALDAY)

image

Finally you must integrate ZCKF_A_STCK_QTY or ZCKF_A_STCK_VAL CKF’s into Rows section of your query, in which months are at the columns, as a hidden key figures.

You must add these hidden KF’s to the OPENING STOCK VALUE or OPENING STOCK  QUANTITY of the month in a formula in the rows to get the DAILY AVERAGE STOCK QUANTITY or VALUE of the month.

DAILY AVERAGE STOCK QUANTITY =  OPENING STOCK QUANTITY + ZCKF_A_STCK_QTY

DAILY AVERAGE STOCK VALUE =  OPENING STOCK VALUE + ZCKF_A_STCK_VAL 

This is somewhat a tedious work and but the result is glorious. I hope you’ll benefit from my experience.

To report this post you need to login first.

1 Comment

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

  1. Sebastian Gesiarz

    Hi Okay,

     

    Could you please tell if there is a way to overcome 7.4 limits of ‘calculation after aggregation’ tick off not being supported? (Formulas before Aggregation are no longer supported. BRAIN_DEV 649 )

     

    Thanks,
    Sebastian

    (0) 

Leave a Reply