Percentage Change in Attributes

[srobshaw]

Hi all,

I'm hoping that someone out there might be able to help me, as business objects technical support don't seem capable !!

What I'm trying to do is create a report which contains a filter which allows you to select a week number. e.g the value 25 in the table data below.

Then I want the report to show the week selected and the percentage change of an attribute in this example NUM_ACTIVE from the previous week e.g. week 24 in the data below.

There should only be one row per week, and there may be gaps in the week numbers.

I have no idea how to structure the universe / report to do this. What i think i want to do is effectivly bring the current week row and the previous week row back in one row selected from SQL but have no idea how to do this.

Further details below

Database is Oracle 902

Fact Table: F_KMR_SUMMARY

Table Columns
DATE_CREATED Date
DATE_UPDATED Date
FK_WEEK_IN_CONVERSION Number
NUM_ACTIVE Number


Fact Table Data:
DATE_CREATED DATE_UPDATED FK_WEEK_IN_CONVERSION NUM_ACTIVE
19/05/2004 19/05/2004 24 0
19/05/2004 19/05/2004 25 3


If anyone can help it would be much appreciated.

Regards

Simon

 

[blom0344]

Do you have the ability to modify the universe / add new objects?

You can build a view that stores data in a de-normalized fashion, storing the week data column-wise, instead of row-wise.

Alternative is to create an additional object that stores week-1 as a dimension. Now create a report based on 2 dataproviders (1st DP with the week dimension / 2nd DP with the modified dimension) and link over the the two week dimensions.

This way you can compare week 24 and week 25 data within one table row.

Thirdly, a piece of free-hand SQL will do the trick as well(using temporary sets), but you probably will not want this.........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DJM99]

I think the easiest way would be to use 2 data providers.

Firstly create a new object in the universe called previous week, make the definition = date - 7.

In the first Query use the objects
Date and the measures

In the second query use
Previous Week object and the measures you want to compare.

You can then link the data providers on Date and Previous Week, allowing you to create the variance measures you want.


Darren Marsden

http://www.BIMentors.com

<-Sign up for our BusinessObjects® Advice Newsletter