 Former Member

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 Create a Moving Average in Webi Report

BOBJ Tricks: Moving Average in Webi

Regards

10|10000 characters needed characters exceeded Former Member
Apr 25, 2014 at 08:03 PM

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

10|10000 characters needed characters exceeded
• Former Member
Apr 17, 2014 at 03:32 PM

10|10000 characters needed characters exceeded
• sampath guntha Former Member

Don't use Runningaverage() function already you are calculating average in variable take the runningsum() and try once.

Thanks,

G Sampath Kumar

• Former Member
Apr 18, 2014 at 12:34 AM

Hi,

Try using =[Measure]/Count([Date])

for average. Then use RunningSum or RunningAverage as per the requirement.