BusinessObjects Board

How to get an interpolated result in a report?

building reports desktopintelligence
#1

I’m trying to get a value from a report set based on linear interpolation. For example if you create a report using eFasion with Week and Sales Revenue, then create a RunningSum function for the Sales Revenue, you’ll get results like this:

Week	Sales revenue	Accum
1.00	$503,580	$503,580
2.00	$847,028	$1,350,608
...
11.00	$743,874	$8,437,948
12.00	$752,468	$9,190,416
13.00	$861,213	$10,051,629
...

My need would be similar to trying to determine the exact point (linearly interpolated week) at which the cumulative sales hit $10M, which can be defined as:

10M Week = ((10000000 - {ACCUM_UNDER_10M}) / ({ACCUM_OVER_10M} - {ACCUM_UNDER_10M})) * ({WEEK_OVER_10M} - {WEEK_UNDER_10M}) + {WEEK_UNDER_10M}

Where
{ACCUM_UNDER_10M} = Highest accumulated sales revenue under 10M
{WEEK_OVER_10M} = Corresponding week
{ACCUM_OVER_10M} = Lowest accumulated sales revenue over 10M
{WEEK_UNDER_10M} = Corresponding week

The value ends up being 12.94005 in this case.

The problem I have lies with Reporter’s limitations when trying to define the nearest cumulative revenue and corresponding weeks to the $10M mark. Doing functions like =Max() Where ( < 10000000 ) results in syntax errors.

Anyone know how to get around Reporter’s limitations here? I’m running v6.1b. Thanks.

paulmaric (BOB member since 2004-02-09)

#2

For starters, please find this FAQ entry.

Andreas :de: (BOB member since 2002-06-20)

#3

I tried that, but it didn’t help, since the variable I need to use in the Where clause is a measure. The boolean variable is then forced to be a measure, and Reporter gives a Syntax Error (DMB0007) when using it in the Where clause.

Is there no way to use a measure variable object in the Where clause of another variable? I also tried converting all my data provider objects to dimensions, but because I then build variables in stages on top of those using Runningsum and Runningproduct functions, it forces those variable to be measures.

Why does Reporter limit the use of variables so much?

paulmaric (BOB member since 2004-02-09)