Usage of RunningSum() function in WEBI
Now I want to share information about RunningSum() function in Webi Intelligence.
Here is a small example.
You have a report that shows the Actual & Plan values for each Category for the current Fiscal Year monthly and YTD.
The Data is coming from SAP BI on an monthly basis. Here you need to find the YTD values on each month for each category.
That means YTD of May = YTD of April + MTD of May and YTD of June = YTD of May + MTD of June
Using RunningSum() function you can acheive this.
The syntax is = RunningSum(measure[;Row|Col][;(reset_dims)])
So you have Dimension objects as FY Year, Period, Category, and Measure objects as Sales-Actual, Sales-Plan, Rev-Actual, Rev-Plan
Now i want the data to be displayed as
|FY Year||Period||Type||Sales Actual||Sales Plan||Rev Actual||Rev Plan||Sales Actual||Sales Plan||Rev Actual||Rev Plan||Sales Actual||Sales Plan||Rev Actual||Rev Plan|
To achieve this task you need to create a crosstab with fields FY Year in 1st column, Period in 2nd Column, Category in 3rd Column and Months in 4th column.
Also create a variable with YTD Sales Actual as shown below
I passed Measure object (Sales figure in MUs) and Dimension object (LO1 Type) as i want the values to be reset to every Category.
Like wise create variable for rest of the measures like Sales-Plan, Rev-Actual, Rev-Plan as shown below( here I am showing for Rev-Actual)
Now, the output will be as shown below
if you are not using the dimension object in the runningsum() function as shown below.
Then the output would be like this as shown below which is giving the wrong YTD values for GEN and TRAN categories.
Also if you want for Monthwise reset in the runningsum() function give Dimension Object [month] as shown below.
Then the output will be as shown below with clear difference as marked in colored rectangles as discussed above the formula about the calculation of YTD values
Hope this might help for people who need better clarity about the usage of the function.
Note: I divided the formula with 10000000 value as you see in formula editor. It is because I want to show the values in Rs. Crores.