Skip to Content

Hi Friends,

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

APRIL MAY JUNE
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
2013 MTD DIST 547.85 529.01 277.52 337.77 565.84 523.62 289 334.82 583.43 570.41 294.7 362.53
2013 YTD DIST 547.85 529.01 277.52 337.77 1,113.69 1,052.63 566.52 672.59 1,697.12 1,623.04 861.22 1,035.12
2013 MTD GEN 443.09 459.73 237.39 198.37 455.25 469.72 240.94 200.19 473.8 473.97 263.99 217.38
2013 YTD GEN 443.09 459.73 237.39 198.37 898.34 929.45 478.32 398.56 1,372.14 1,403.42 742.31 615.93
2013 MTD TRAN 0 0 24.73 24.73 0 0 24.73 24.73 0 0 19.73 24.73
2013 YTD TRAN 0 0 24.73 24.73 0 0 49.46 49.46 0 0 69.19 74.18

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

runnigsum variable1.png

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)

runnigsum variable2.png

Now, the output will be as shown below

final runningsum.png

if you are not using the dimension object in the runningsum() function as shown below.

runnigsum variable3.png

Then the output would be like this as shown below which is giving the wrong YTD values for GEN and TRAN categories.

/wp-content/uploads/2012/11/runingsum2_160975.png

Also if you want for Monthwise reset in the runningsum() function give Dimension Object [month] as shown below.

runnigsum variable4.png

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

/wp-content/uploads/2012/11/runingsum3_160984.png

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.

Cheers

RAM

To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply