Skip to Content
avatar image
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

Anyone please help.

Regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 17, 2014 at 03:32 PM

    Please provide some sampleĀ  data and the expected results to us be able to help your more..

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      If you are using multiple years in your report, this may bring multivalue error. You can try adding [Year] and [Time]  also to the end of the formula:

      =NoFilter(RunningAverage([TagMeasure];([v_Month]))) foreach([Day]) in ([Year];[Month];[Time];[TagName])


      I wrote at my first answer [TagMeasure] instead of [TagName] by mistake at the end of the formula. You should change it ofc.


      Hope this one helps.