nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Calculating % change from values in one column

EmmanuelMash

Hi Legends

I have a small scenario, if you have data in one column as shown in the table below and i need to see how I have grown from 155 to 250 and from 155 to 240 etc as we would do in excel with the growth formula (a1-a2/a2) * 100, would have input into how we could achieve the same in OBIEE?

Regards

Emmanuel

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Hi Emmanuel,

Have you tried simply calculating with the formula you've just written?

If yes what was the result or - sinve you are posting here - problem?

If no...why not?!

EmmanuelMash

Hi Christian

The formula is correct but my question is how can we do it so as to reference the growth from 155 to 210 in obiee. How can you use the contents of the position 155 like we would in excel i.e cell a1 for example.

regards

Emmanuel

Joel Acha

Not sure what your underlying database is but Oracle for example has LAG and LEAD Analytic functions so you can push this down to database by using the OBIEE EVALUATE function.

http://gerardnico.com/weblog/2009/04/17/obiee-period-to-period-comparison-with-the-analytical-function-laglead/

[Deleted User]

"cell" just like "row" are completely virtual concepts based on the business model makeup.

Physical vs derived calculations as well as analysis-based formulae kick in and hence post-agg vs pre-agg.

You show multiple measures and one single dimensional attribute but impmicitely split the results across more dimensions/dimensional attributes which you are not showing and so are making the question itself imprecise. Hint: query and result grains are important.

Yes, analytic/windowing functions are a part of the answer but with such a vague problem description one can't well give more than a conceptual answer.

Gianni Ceresa

Christian already gave you a big piece of the answer.

I personally wouldn't disturb analytic functions of your DB because 1) you maybe don't have an Oracle database as source and so LEAD/LAG are useless and 2) there are still ways to do it in OBIEE ...

What makes one row different from the other?

What define the order of these rows? (because 155 - 210 - 240 is different than 210 - 155 - 240, the % will have a completely different meaning)

Walter de Wit

Hi,

in case there is time involved you probably can use the 'Time Series' function Ago() for this. With this you can compare a value to a period ago.

[Deleted User]

True I assumed an Oracle DB. Plus looking at this on a normal-sized screen I just realized that the second column is an attribute which breaks down the rows.

Still looks like a random attribute though rather than sth hierarchical or sth with a deducible logical progression.

Gianni Ceresa

I guess the same but as he blurred a lot I'm not going to guess more and wait for his answer

EmmanuelMash

Thank you all for the input. I am using an oracle DB so will try to LAG/LEAD. The blurring was only because of the data sensitivity but we could make sense of this using something else.

The question is from a planning model where I need to know what the impact of changing certain percentages will impact the expected result. For example lets say this is a Cotton plantation, if we increase what we plant by the factors below from 2.5 to 3.0 then we get a harvest of 55000 tonnes. There is no need to calculate the growth for 2.5% as it is the starting point. It only becomes relevant from 3.0% but these values can be anything based on selected value from a list of values. This is on an OBIEE analysis and like @Christian Berg's qsn 'Physical vs derived calculations' the results are calculated on the fly based on the selected increase factors. Sorry my initial question was a bit vague I was looking just for a way to calculate growth as we would on excel file as shown below :

scenario:

Increase Factor by Result Growth

2.5% 40000

3.0% 55000 (55000-40000)/50000

4.0% 60000 (60000-50000)/60000

Excel

OBIEE

???

Walter de Wit There is no time involved so this is just values. @Gianni Ceresa and @Christian Berg does the explanation above make it any better. I just want to give someone who is planning the impact of increasing by a certain factor. Is this doable in an OBIEE analysis.

Gianni Ceresa

Thanks, make sense (the "increase factor by" was the mysterious part ).

I still believe there is no need to disturb LAG / LEAD.

You can get the value of the previous row for your calculation by using MSUM() and some logic.

MSUM("your column", 2) - "your column" = the value of the previous row of "your column"

You now have everything you need to make your calculation, add a CASE WHEN to not calculate it for the first row.