BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

How to Create a Moving Average in Webi Report

Goto page 1, 2  Next
 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
mjyoungman
Principal Member
Principal Member



Joined: 06 Oct 2006

Posts: 110
Location: Sydney


flag
PostPosted: Mon Nov 19, 2007 5:15 pm 
Post subject: How to Create a Moving Average in Webi Report

I'm using Business Objects Webi SP2/Prod Pack.

I'm trying to figure out how to do a moving average calculation (say for 3 days prior) so I can graph it next to actual measure I'm reporting by day. For example my data looks like this:

Date Amount
1/1/07 100
2/1/07 200
3/1/07 150
4/1/07 300
5/1/07 75
6/1/07 100

So the above makes a nice line graph...but I want to also track the moving average against this (eg 1/1/07 would be 100, 2/1/07 would be (100 + 200)/2 = 150, 3/1/07 would be (100 + 200 + 150)/3 = 150, 4/1/07 would be (200 + 150 + 300)/3 = 216.66.

But I can't figure out what the Webi forumla would be to calculate this going back 3 days at a time.

Please help - this is doing my head in!

Thanks!
Back to top
buggi
Principal Member
Principal Member



Joined: 27 Jan 2006

Posts: 130


flag
PostPosted: Wed Nov 21, 2007 2:29 pm 
Post subject: Re: How to Create a Moving Average in Webi Report

Actually it is like a running sum(function in BO)....search for running avg other wise create a formula based on the requiremnt...
Back to top
mjyoungman
Principal Member
Principal Member



Joined: 06 Oct 2006

Posts: 110
Location: Sydney


flag
PostPosted: Wed Nov 21, 2007 10:29 pm 
Post subject: Re: How to Create a Moving Average in Webi Report

Thanks for the feedback, however a running sum doesn't do it, and whilst there is a running average, it's looking for a reset function, but I'm not sure how to tell it to look at the last 3 days for example. I need a little more information to get this to work. Thanks anyway.
Back to top
shevr
Senior Member
Senior Member



Joined: 27 Apr 2006

Posts: 43


flag
PostPosted: Fri Nov 30, 2007 4:19 pm 
Post subject: Re: How to Create a Moving Average in Webi Report

create a report variable and code as follows:

([YourMeasure] + Previous([YourMeasure]) + Previous(Previous([YourMeasure]))) /3
Back to top
mjyoungman
Principal Member
Principal Member



Joined: 06 Oct 2006

Posts: 110
Location: Sydney


flag
PostPosted: Mon Dec 03, 2007 1:07 am 
Post subject: Re: How to Create a Moving Average in Webi Report

Thanks So Much - That worked a treat...

I didn't realise you could have a previous on a previous....

Muchly appreciated! icon_biggrin.gif
Back to top
OpusDei
Senior Member
Senior Member



Joined: 20 Aug 2009

Posts: 38
Location: Berlin, Deutschland


flag
PostPosted: Mon Jun 07, 2010 6:04 am 
Post subject: Re: How to Create a Moving Average in Webi Report

Cool - thx a lot icon_exclaim.gif
Back to top
David Gilbertson
Senior Member
Senior Member



Joined: 08 Mar 2011

Posts: 35



PostPosted: Thu Apr 21, 2011 5:35 am 
Post subject: Re: How to Create a Moving Average in Webi Report

You could also do it without nesting previous statements with a formula like this:
=(RunningSum([Revenue]) - RunningSum(Previous([Revenue]; 5))) / 5

So you can have an average or as many as you like.

You can wrap the whole thing in the NOFILTER() function so that you can hide the first x rows and still see the moving average.

Taken from this post:
http://blog.davidg.com.au/2011/04/moving-average-in-webi.html
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Thu Apr 21, 2011 6:01 am 
Post subject: Re: How to Create a Moving Average in Webi Report

In addition to what has already being said, if you require use of a No Filter function, you can also look at the RelativeValue() function.
_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
SRR
Principal Member
Principal Member



Joined: 20 Jan 2009

Posts: 105



PostPosted: Wed Apr 27, 2011 6:59 am 
Post subject: Re: How to Create a Moving Average in Webi Report

In BOXI R3 version, RunningAverage() is a function that can be used for getting the output.
Back to top
David Gilbertson
Senior Member
Senior Member



Joined: 08 Mar 2011

Posts: 35



PostPosted: Wed Apr 27, 2011 5:32 pm 
Post subject: Re: How to Create a Moving Average in Webi Report

Hi Mak1,

I haven't been able to get NoFilter to work with RelativeValue. I have a report showing revenue by quarter (I'm only sort of hijacking the thread). I want to be able to to show all quarters, and for each, have a column showing the value for the same quarter last year. So I'm grouping by year/quarter and using relative value by year.

But now I also want to be able to filter the report to only show the current quarter. When I filter for this, my values for the previous year disappear, even though I have the formula
=NoFilter(RelativeValue([Total Revenue]; ([Fiscal year]); -1))

Any ideas? I can't use previous, because for each quarter I have a varying number of rows (another dimension).
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Thu Apr 28, 2011 1:05 am 
Post subject: Re: How to Create a Moving Average in Webi Report

Hi David,

NoFilter only applys to the actual result of the calculation. i.e. it will display the value of the measure "ignoring" any page or table filters applied.

For example, if I have a table filtering out an employment group of contractors, displaying headcount, but, I also wish to have a column, in the same table, showing contractor numbers, then I would do something like:-

Code:
NoFilter([Headcount]Where([EmploymentGroup] = "Contractors"))


Cheers,

Mark.

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
David Gilbertson
Senior Member
Senior Member



Joined: 08 Mar 2011

Posts: 35



PostPosted: Thu Apr 28, 2011 1:49 am 
Post subject: Re: How to Create a Moving Average in Webi Report

Hi Mak,

Yes, but the thing is, if I use the RelativeValue function, then NoFilter doesn't seem to work.

I have a report where each row is a month. I want a variable that shows the value from 12 months ago. Both Previous and RelativeValue functions work as expected - no problems there.

But then I filter out the first 12 months from the report, and use NoFilter so that I can still access data from those 12 months. This works for the Previous function, but not for the RelativeValue function.

Any ideas?
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Thu Apr 28, 2011 5:55 am 
Post subject: Re: How to Create a Moving Average in Webi Report

Hi David,
Unsure I have only ever used this function in conjunction with a Where Clause.
In the following code example I had a chart that displayed, via a filter 13 months, lets say for argument Dec > Dec.

However, in % calc I wanted the previous function to jump back 14 months i.e. Novembers Value. I acheived this by creating the following variable. This may, or may not, give you some ideas.

Code:
=(NoFilter(([Headcount Operational 14 Month]Where([EmployeeGroupCustomised] = "Perm" And[Business Area Grouping] = "Middle Office")))-NoFilter(RelativeValue([Headcount Operational 14 Month];([FutureBaseCalendarDateMMMyyyy]);-1)Where([EmployeeGroupCustomised] = "Perm"And[Business Area Grouping] = "Middle Office")))/NoFilter(RelativeValue([Headcount Operational 14 Month];([FutureBaseCalendarDateMMMyyyy]);-1)Where([EmployeeGroupCustomised] = "Perm"And[Business Area Grouping] = "Middle Office"))


Cheers,

Mark.

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Swimmer
Forum Associate
Forum Associate



Joined: 23 Aug 2006

Posts: 569


flag
PostPosted: Fri Apr 27, 2012 7:04 pm 
Post subject: Re: How to Create a Moving Average in Webi Report

Very nice post. Thanks
Back to top
OmeWillem
Forum Member
Forum Member



Joined: 12 Sep 2007

Posts: 12
Location: Heerlen / Maastricht


flag
PostPosted: Fri May 03, 2013 8:22 am 
Post subject: Re: How to Create a Moving Average in Webi Report

icon_biggrin.gif icon_biggrin.gif

This is just what i was looking for, thanks Guys
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 2 All times are GMT - 5 Hours
Goto page 1, 2  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0326 seconds using 17 queries. (SQL 0.0027 Parse 0.0010 Other 0.0290)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo