Running AVG - problem with section

building reports webintelligence
1

Hi all,

version XI 3.1 SP3

I’m developping a report with multiple tables and graphs where the section is used as a bookmark for reportbursting into several pdf files.

So far the last obstacle is the running average. This works perfectly, just not when there is a section added.

In my example in EFashion, i have used following variables and a section on State.

running avg year N : =NoFilter(Previous(Previous(RunningSum([Sales revenue]);0)-Previous(RunningSum([Sales revenue]);12);0)/12)
running avg year N-1 : =NoFilter(Previous(Previous(RunningSum([Sales revenue]);0)-Previous(RunningSum([Sales revenue]);12);12)/12)

i used sum N and sum N-1 to add a context to the variables

sum 2012 : =(Sum([J(N) 12 month runavg] In([State];[Year];[Month])))
sum 2011 : =(Sum([J(N-1) 12 month runavg] In([State];[Year];[Month])))

but somehting goes wrong in the context I suppose. (might not be the shortest way of defining variables btw)

Webi now calculates the running average over the LAST 12 LINES.
for N-1 → calculates over partly lines CURRENT SECTION AND PARTLY OVER PREVIOUS SECTION …
→ see State ‘Colorado’ : the average is far to high, as webi calculates over revenues of ‘California’ as well.

how can i fix this? how do i define the context correctly so webi only calculates within the current section?

file should be added (new here, so please reply if i didn’t do so correctly)
RunAVG EFashion.wid (107.0 KB)

jos (BOB member since 2016-09-26)

2

some progress… :slight_smile:

i found that there is a Reset_Dimenstion needed.
for a simple runningsum this would be

=RunningSum([Sales revenue];([State]))

this works fine, but in the running average formulas this doesn’t work.
i’ve tried to imply this as followed : (for a 3 month average this time, is more apprehensive than 12 month)

=NoFilter(Previous(Previous(RunningSum([Sales revenue];([State]));0)-Previous(RunningSum([Sales revenue];([State]));3);0)/3)

jos (BOB member since 2016-09-26)

3

Have you tried playing with the context keyword, “section”?

Mak 1 :uk: (BOB member since 2005-01-06)

4

hi mak,

u mean a context as “In Section” ?

this returns the total of the revenues in the section, but the total on every line in the current section.

well, im stuck, im a bit clueless i suppose cause i can’t seem to figure out why the syntax is not right.

jos (BOB member since 2016-09-26)

5

=NoFilter(Previous(Previous(RunningSum([Sales revenue];([State]));0)-Previous(RunningSum([Sales revenue];([State]));3);0)/3)

the formula with resetdimension on state works fine ONLY if i remove the NoFilter ->

=(Previous(Previous(RunningSum([Sales revenue];([State]));0)-Previous(RunningSum([Sales revenue];([State]));3);0)/3)

However, when “NoFilter” is not added to the formula and 2003 is selected for example, the average is of course calculated on the filtered data… not what im looking for.

is there anyway to combine those? or is that simply not possible in BO?

jos (BOB member since 2016-09-26)

6

The previous function can be notoriously tricky, especially when it is nested multiple times like yours.

Is there no “help” you could request from your database team?

Mak 1 :uk: (BOB member since 2005-01-06)

7

not really no.
i could contact the firm that maintains the DWH but i suppose they are not so keen on looking into custom variables and formulas like these.

could there be a different approach than the nested previous functions that works better?

anyway, cant be i’m the first person trying this right?

jos (BOB member since 2016-09-26)

8

RelativeValue() is another variable you can use for this sort of thing.

I did find a post of mine from many moons ago, which may help

https://bobj-board.org/t/97416/12

Plenty of results on Google too.

https://www.google.co.uk/webhp?gfe_rd=cr&ei=j83WV-TBMYie-wbN07vIBw&gws_rd=ssl#safe=active&q=running+3+month+average+business+objects+webi+section

Mak 1 :uk: (BOB member since 2005-01-06)

9

thanks Mak, u pointed me in the right direction.
RelativeValue does the trick.

jos (BOB member since 2016-09-26)

10

Oh, sweet. :+1:

It was so long ago, but I know it was a little more powerful than the Previous function.

Can you post the formulas on here, it may help someone else, if you have time?

Mak 1 :uk: (BOB member since 2005-01-06)

11

sure. hope i don’t find any bugs in it over the next days :slight_smile:

created variables in 2 steps :

first RunningSum with reset dimension on the section
=RunningSum([Sales revenue];([State]))

2 variables for Running AVG :
J(N) 12 month runavg = =( NoFilter(RelativeValue([RunningSum];([Year];[Month]);0)) - NoFilter(RelativeValue([RunningSum];([Year];[Month]);-12)) )/12
J(N-1) 12 month runavg = =( NoFilter(RelativeValue([RunningSum];([Year];[Month]);-12)) - NoFilter(RelativeValue([RunningSum];([Year];[Month]);-24)) )/12

in this case AVG’s over 12 months. so be sure to query more data then needed to calculate the N-1 and filter out in table/graph.

jos (BOB member since 2016-09-26)

12

Nice one Jos, Thank you, as you can see it is a question that is asked fairly often :).

Funnily enough, I couldn’t even remembered I had written a post about that very subject.

Must be getting old :mrgreen:

Mak 1 :uk: (BOB member since 2005-01-06)