Let's think over this SAP BW - BeX Query Designer issue:
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:
ZCKF_MONTH_DAYS : Number of days in month (long if expression using ZFVAR_MONTH and ZFVAR_CALYEAR, calculation after cumulation flag is set off)
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)
ZCKF_DATE_VALUE : Number of day in month (ZFVAR_CALDAY MOD 100, calculation after cumulation flag is set off)
ZCKF_DAY_SPREAD : ZCKF_MONTH_DAYS_P1 - ZCKF_DATE_VALUE (Difference of a day from the first day of next month)
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)
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)
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.