Formula column - calculation based on value from other row — 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

Formula column - calculation based on value from other row

Received Response
22
Views
2
Comments
Sriram Natarajan
Sriram Natarajan Rank 1 - Community Starter
edited Aug 13, 2024 7:09PM in Oracle Analytics Cloud and Server

Hi Friends,

I am new to OBIEE. I have a requirement to create formula column based on value from other row.

For eg:

-----------------------------------------------------------------------------------

rownum Account_num     Account_Desc      Amount

-----------------------------------------------------------------------------------

1            100                   Gallons Propane     60000

2            200                   Gallons Fuel          10000

3            300                   Propane Revenue   150000

4            400                   Fuel Rev                40000

-----------------------------------------------------------------------------------


I want to a New Column "Derived Col"  and for Account_num = 300, (Amount value of Account_num 300)/ (Amount of Account_num 100)

                                                                   Account_num = 400, (Amount value of Account_num 400)/ (Amount of Account_num 200)


How to refer the column value of row number 1 in row number 3?

Can you please help?

Thanks,

Sriram

Answers

  • AkanshAgarwal
    AkanshAgarwal Rank 4 - Community Specialist

    Hi Sriram,

    You can use filter function as FILTER("Amount" USING Account_num = 300 )

    Regards,

    Akansh

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    you'd be better off with a common code for Fuel/Propane and a units of measure (UOM) column (the account numbers are getting in your way) ... then you can do the FILTER() referenced above, in the RPD, and consistently get the calculation of average dollars per gallon independent of the account number.

    IE:

    Row     Account_num     Account_Desc      Amount  account_type  UOM

    1         100                   Gallons Propane     60000    P                    gal

    2         200                   Gallons Fuel          10000     F                    gal

    3         300                   Propane Revenue   150000   P                    dol

    4         400                   Fuel Rev                40000    F                     dol

    Average Dollars per Gallon (Fuel) = FILTER("Amount" USING account_type = 'F' and UOM = 'gal' ) / FILTER("Amount" USING Account_type = 'F' and UOM = 'dol' )

    Average Dollars per Gallon (Propane) = FILTER("Amount" USING account_type = 'P' and UOM = 'gal' ) / FILTER("Amount" USING Account_type = 'P' and UOM = 'dol' )

    Average Dollars per Gallon (All) = FILTER("Amount" USING UOM = 'gal' ) / FILTER("Amount" USING UOM = 'dol' )




    Use of information drives your data model design ...