Formula column - calculation based on value from other row

Question

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

Thomas Dodds · Answer

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 ...

AkanshAgarwal · Answer

Hi Sriram,

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

Regards,

Akansh