Skip to Content

# Trend Analysis, Comparing Two Metrics with Web Intelligence

## Introduction

This is a quick how to for comparing two time based metrics in order to determine the relative differences in their trends. A typical example of this is comparing two stock prices where you are looking to determine which has performed better over a given period.

The technique deployed here is to chart the two metrics where we set a base value of 100 for both metrics at the start of our period. An example of such a chart is shown below,

Figure 1 Example Trend Analysis Chart

We also see this sort of analysis used quite often in articles by the Economist such as this articlecomparing the difference in price of cereals. Another example is seen in Yahoo Finance when we use their stock chart to compare two stock prices.

To calculate these charts we divide each value by the initial value and then multiply by a hundred to express as a percentage,

`Trend Value = ( actual value / initial value ) * 100`

## Web Intelligence

We’ll now look at how to generate this using Web Intelligence and in this example we’re using Web Intelligence XI 3.1.

Here we going to build a chart comparing two stock prices of two competitors – IBM and Accenture, the query is returning the following objects,

• Stock – the symbol for the company whose stock price we’re trending
•

• Closing Price – the closing stock price value
•

• Date – the date of the closing stock price

The data is weekly and the closing price is the price of the stock at the end of the week. The data covers the period Jan 2010 to Nov 2010. The screen shot below displays a chart in Web Intelligence of the two stock prices and we can see that the price of IBM is higher than that of Accenture but the question is: which has performed better over the time period?

Figure 2 – Stock Prices for IBM and Accenture

First we need to calculate our trend value. To do this we create a new variable that returns the initial value for each stock and for this we use the First() function which returns the first value in a data set. However we need this to reset for each change in Stock object and so we define our variable as,

`Initial value =First([Closing Price]) In ([Stock])`

Once we’ve created this variable we can then create a new variable for Trend Value and this is simply,

`Trend Value =([Closing Price] / [Initial Value])*100`

Both of these are of type measure. From the templates tab we then add a Vertical Mixed line chart to our report. We then add our Trend Value variable to the measures area, Stock to the dimension area and Date to the x-axis dimension area. We now see the relative change in stock price between the two stocks as displayed in the screenshot below.

Figure 3 – Trend Analysis of stock prices showing relative change in stock price

Here it is apparent that the change in price is quite close with IBM just ahead of Accenture by end of the time period. If we look back at our original chart (figure 2 above) it should now be obvious that using this trend analysis it is much easier to establish which is the better performing stock.

The simplest way to interpret these charts is to consider investing \$100 in each stock at the beginning of the period. Since the chart is base lined at 100 at the beginning of the period the chart then shows the return on this \$100 investment over the period.

So we can see that for most of 2010 we would have lost money as our trend line falls below the \$100 mark and its only in September that we would have made a profit. At the end of the period we see IBM edging ahead of Accenture with a value of about 111 while Accenture is at 104. So if we invested \$100 in both stocks we would get back \$111 from our IBM investment and \$104 from Accenture.

4 Comments
You must be Logged on to comment or reply to a post.
• Hi,

i am new to developing the Charts in Webi.i am using BO XI R2.my requirements is as below.

Selling price,cost and variance are the 3 measure objects which displayed in the Graph(i.e Vertical grouped).i placed all these 3 measure objacts on Y-axis and Item name dimension objec on X-axis.the user will input the measurement units like (unit(s)(1),thousand(s)(1000) and million(s)(1000000) based on this the above three measure objects should display the respective values in the Graph.

1) Units will display the values as is.
2) Thousans should display values in terms of Thousands.

my issue is with displaying in Thousands.
when i divide the variance by 1000 the graph output looks empty.(i.e no graph is displaying).

i am very delightful if any help is provided in resolving this issue?

• Hi Nanda Kishore,

As per my understanding, if the variance is calculated based on the price and cost, calculate it after divide price and cost by 1000. don’t divide the variance.

If the variance is calculated in the universe level or database leave, check the with universe designer to resolve this issue.

• dear Alex, The Article was divine, so refereshing to hear from somebody with a decent grasp on the English language and an ability to communicate. I Couldn’t put it down ! Keep up the good work !
• Interesting Article, thanks for sharing it with us