HOw to calculate 12 Months rolling Average

[Former Member]

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.co...

How to Create a Moving Average in Webi Report

BOBJ Tricks: Moving Average in Webi

Anyone please help.

Regards

[Former Member · Accepted solution]

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

[Former Member]

Hi,

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

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

[Former Member]

Did not work.

[Former Member]

Hi,

You can try updating your code like this:

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

[Former Member]

it gave me a #Multivalue error.

[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.

[Former Member]

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

[Former Member]

Sample Data

BATID   Date               Time               Test Value

25646    3/1/2014         01.00.00          11.65

25646    3/1/2014         02.00.00          10.89  

25646    3/1/2014         02.00.00          12.43

25646    3/1/2014         04.00.00          11.71

........         ........         ............            ........ 

.......       ............      ..............          ..........

25646    3/1/2014         24.00.00          10.62

.........     ..............     ..............          ...........

25646   3/31/2014        24.00.00           11.36

In this example for 3/1/2014 (just one day) I have 24 records and average for them is 11.62. For whole month I have 744 records and Average is 10.45.

For Feb, I have 24 records for one day but 672 for whole month as it has only 28 days.

For April till now I have 384+10 as its 10.49 cst right now.

I hope I am clear.

Regards

[CarlyThomas]

Hi Shalini,

So are you trying to perform a running average for the month that resets on each new month?

You can try a formula similar to RunningAverage([Measure]).

If you want it to reset for each month, you'd add a section on the month in your table and use a formula similar to:

=RunningAverage([Measure];[Month Dim])

Does that help?

Thanks,

Carly

[Former Member]

Thanks Carly..I tried that but It did not work

[CarlyThomas]

Thanks, Shalini.

Are there multiple breaks or sections in your report?  How is it not working?

Are you able to share a screenshot showing what's happening and also more details on the expected results?

I see your table above, but I'm not sure on where you want to see the Averages and where it is expected to reset or not reset.

Also, what version of WebI are you using?  Some calc engine changes can cause differing results between versions.

Thanks!

Carly

[Former Member]

Attaching a file here

here is the syntax I am usnig for Average

The cloumn with DAliy AVerage has following syntax..its named wrongly as DAily. It should be MOnthly. Rolling.

=NoFilter(RunningAverage([TagMeasure];([v_Month])))

[CarlyThomas]

Can you try putting a break on v_Month and see if that will get you the results you're looking for?

Thanks,
Carly

[Former Member]

Did not work.

[former_member183330]

Hi Shalini,

What is the Logic you used in"[v_Month]" variable.

Thanks,

G Sampath Kumar

[Former Member]

I am getting the month from Date.

[former_member183330]

Shalini,

I think you don't need moving average, you need moving months average.

Please correct me if I am wrong.

Thanks.

[Former Member]

Yes..you are right!

[former_member183330]

Hi Shalini,

Use the below variable may be it will give the expected result:

=[Test Value]/Count([BATID];All)

Please find the screen shot attached.

Thanks,

G Sampath Kumar

[Former Member]

Thanks..It gives me correct monthly average  but I need rolling last 12 months so First month say on  Jan  2, 2014 it should give me an average for last 365 days from 3 Jan 2013 till 1 Jan 2014. That is not coming correctly.

[former_member183330]

Shalini,

When you are calculate the average it will consider the dimensions which you included in table.

In shot the month wise average is coming correct for you but if you want for year it should be the sum of month wise average amount. it is weighted average calculation, if you under stand the logic then try your self other wise give me the objects list which you are trying and the table structure how you want to display

Thanks,

G Sampath Kumar

[Former Member]

yeah..understand that...so I was writing  =Runningaverage([Measure];([v_year];[dimension])

In one of the post above I have given table structure and mock up data.

Thanks for helping me on this.

Regards

[former_member183330]

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

Thanks,

G Sampath Kumar