Moving Average Total of Key Figure in Bex Query
Hi BI Experts,
I would like to share the recently learnt and tried out concept here in SDN.
This is about the calculation of Key Figure values for the past 12 Calendar Month in Bex Query Level.This will be called as Moving Average Total.
Please find below the steps to be followed.
1. Create a simple BEx Query if you are practising for the first time with single Key Figure (Doll) in the Query.
2. Drag the “Number of Records” from the left pane (Info provider List) and keep it in Column Section of the Bex Query where all the Key Figures are placed.
3. Now put the “Number of Records” as “Always Hide’ by going to its properties.
4. As a next step, create the new selection in column section and name it as “MAT-Doll” .Create a customer exit variable for 0CALMONTH and placed it under the selection “MAT-Doll” and range it with of 0 to 11 as shown below.
5. We have successfully completed the procedures to be done in BEx Query level and as a next step we need to write the customer exit in program ZXRSRU01.
6. Go to se38 and select the program ZXRSRU01 and write your code as pseudo code as below.
Don’t forgot to assign your inputted CALMONTH into MMONTH after the When Statement.
This will give the result as follows.
IF the input is given for the CALMONTH as 02/2012. Then we will get the previous 12 months data.