@MOVSUM and @MOVSUMX functions are handy functions that can used to calculate trailing or forward sums across dimensions. You can benefit from using these functions for calculating rolling sums. Details about @MOVSUMX function from the Oracle tech ref can be found here.
Although the tech ref gives a lot of details, I thought it was worth sharing a practical use of this function and something worth noting regarding the time period range used in the XRangeList as I have used this a lot of times in Hyperion Planning implementations.
Let’s assume we have the following members in the outline:
MOVSUMRESULT is the member containing the final result
MOVSUMSOURCE is the member containing the data to be summed up.
Say, you need a trailing sum of 3 months crossing years and you write the below code:
MOVSUMRESULT = @MOVSUMX(TRAILMISSING,MOVSUMSOURCE,3,@XRANGE("FY14"->Jan,"FY15"->Dec));The result is as shown below:
You will notice that MOVSUMRESULT Jan FY15 = Jan FY15 + BegBalance FY15 + Dec FY14. Basically the function takes in to account the BegBalance member and hence the results for Jan and Feb are not accurate. The correct calculation should be MOVSUMRESULT Jan FY15 = Jan FY15 + Dec FY14 + Nov FY14
To remove BegBalance from your range, you can use the below code:
MOVSUMRESULT = @MOVSUMX(TRAILMISSING,MOVSUMSOURCE,3,@REMOVE(@XRANGE("FY14"->Jan,"FY15"->Dec),@LIST(FY15->BegBalance)));This code removes BegBalance from your time period range. With this change, you will now get the correct results as shown below:
Extremely helpful as we deal with Rolling sums all the time. Please keep up the great work!
Hi Neha,
Very informative writeup on @MovSum function. I wanted to know for integer(n) in the syntax @MOVSUMX (COPYFORWARD | TRAILMISSING | TRAILSUM, mbrName [,n[,Xrangelist]] ), can we pas through a temporary variable
Eg:
Var Movvar= Days/30 /*Days here is a member which has input form users through data form in planning eg 60, hence Movvar=2*/
Then Pass the value of the variable in the @MOVSUMX
Eg: @MOVSUMX(TRAILSUM,Sales,Movvar,Jan:Aug)
Basically the Movvar tells how many months to consider for summation.
My requirement is taking last n months sum based on variable.
Let me know if this is possible.
Hi Saumya,
Thank you!
Yes, you can use a variable for the integer ‘n’ in the syntax. You need to declare an integer and then assign a value to the integer. I have given a sample below using a cross dim that you can point to with the specific intersection from your web form.
Please note that the value assigned to the variable must be within (less than) the number of members (range) resulting from your XRANGE specification
————————————————————————————-
FIX(&CurrentYear,@Relative(“YearTotal”,0)………)
VAR V_SUM_MONTHS = 0;
“Sales”
(
V_SUM_MONTHS = “Days”->”BegBalance”/30;
“Accum Sales” = @MOVSUMX(TRAILSUM,Sales,V_SUM_MONTHS,Jan:Aug)
)
ENDFIX
————————————————————————————-
Hope this helps!
Regards,
Neha
Thanks Neha, that helps a lot as the rolling accumulation is dynamic based on number months the user wants.
Regards,
Saumya
Hi Neha,
One more query with regards to temporary variable. I can pass a substitution variable to temporary variable.
I know that temporary variables will hold only integer values.
eg : Var v_yr = &BudYr or can be user input through RTP Calculation variable.
Is this possible.
Then this variable is used with some functions in calc scripts.
Please let me know your suggestions.
Regards
Saumya