Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

HOw to calculate 12 Months rolling Average

Hi,

I am working on a webi which should have a calculated column as Rolling Average for last 12 months.

For one day I get 24 records. So for those months which has 30 days I get 720 and with 31 days i get  744.

I researched and referred following threads but nothing worked. Values are still not correct.

Calculating Moving Averages in Web Intelligence | Business Intelligence Articles from www.gulland.com

how to calculate 16 days moving average in WEBI

How to Create a Moving Average in Webi Report

BOBJ Tricks: Moving Average in Webi

Anyone please help.

Regards

Former Member
Former Member replied

Hi,

You might need a couple of steps to get this done.

Step 1: Count the number of days for each month

=Count([Date]) ForAll([Date]) ForEach([Month])

Step 2: Calculate total Test value for each month

=Sum([Test Value]) ForAll([Date]) ForEach([Month])

Step 3: Calculate running count for month (each month has 1 value, i.e Jan = 1, Feb = 2 and so on)

=RunningCount([Date]) ForAll ([Date]) ForEach ([Month])

Step 4: Calculate the total days of the last 12 months

Count =Count([Date]) Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month]) >=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block)-12)

Step 5: Calculate the total Test Value during the last 12 months

Sum =[Test Value] Where (RunningCount([Date]) ForAll ([Date]) ForEach ([Month])>=(Max(RunningCount([Date]) ForAll ([Date]) ForEach ([Month])) In Block -1))

Step 6: Calculate the rolling average

=[Sum]/[Count]

Note: You might create new variables for each step above, but do not use these new variable in the calculations from step 1 to 5. All the formulas above need to be in the exact form. Otherwise the calculate context in webi will fail to generate the expected results.

I hope this helps.

Thanks,

Huu Nguyen

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question