Period Comparison without time series or ago functions.

Recently at a client site we had to connect to some tables in owned by another application and generate some reports for our OBIEE user base. We had no control over the table structures in the external application and we had to generate some time series measures. We couldn’t use the time series wizard or the ago functions provided by OBIEE.

The report had to display all the quarters of the current year and the growth for the same period for each of the quarters.

This is how the repots looks like in a regular table view in answers.

clip_image002

And the picture below shows what the client wanted.

clip_image004

We could have done the same with a complex case expression but we found an alternative which is more elegant and better performing, using pivot table calculations.

First up we need to change the formula for the quarter to the one shown below.

clip_image006

Current year is a repository variable, this could also have been a presentation variable.

And the table view would look like

clip_image008

Now create a pivot table for this with the year on the left and the quarter on the top.

Create 2 calculated items on the year column

The first one (Actuals) as $2 and the second one as $2-$1 (growth) and then hide the details and this would give the sales amt for all the quarters for the current year and the growth.

clip_image010

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: