Calculating % change from values in one column — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Calculating % change from values in one column

Received Response
383
Views
13
Comments
EmmanuelMash
EmmanuelMash Rank 4 - Community Specialist
edited Aug 13, 2024 6:06PM in Oracle Analytics Cloud and Server

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?

Region.png

Regards

Emmanuel

«1

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    EmmanuelMash Rank 4 - Community Specialist

    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
    Joel Rank 8 - Analytics Strategist

    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]
    [Deleted User] Rank 2 - Community Beginner

    "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
    Gianni Ceresa Mod

    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
    Walter de Wit Rank 2 - Community Beginner

    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]
    [Deleted User] Rank 2 - Community Beginner

    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
    Gianni Ceresa Mod

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

  • EmmanuelMash
    EmmanuelMash Rank 4 - Community Specialist

    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

    pastedImage_15.png

    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
    Gianni Ceresa Mod

    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.