Help on BEx cells definition

[Former Member]

Hi Experts

I need advice in a certain type of calculation in BEX query . I need to achieve a formula USDN

USDN = (Avg rate OIL - (%N OIL * USDN AMMONIA )) / %P OIL

I need to take values from two rows (Eg., below from OIL and AMMONIA) to populate the value USDN for OIL

I have defined two structures in BEX and created cells . The problem is that when I define this formula in cell for Product OIL which has only rates for Publication=GM , it is not picking up the USDN Ammonia value which is calculated for Ammonia obviously because AMMONIA does not have values for GM publication .

I want to calculate if product=  oil and publication =GM then

USD/N for OIL= AVG (USD/N for AMMONIA) for the given given calendr day . Is this possible using cells or is any replacement path variable ?

I use analysis for office to show my query output

Product	Publication	Fiscal year/period	Calendar day	Avg rate	%N	%P	USDN
OIL	FER	001.2011	06.01.2011
OIL	FER	001.2011	13.01.2011
OIL	FW	001.2011	06.01.2011
OIL	FW	001.2011	13.01.2011
OIL	BMF	001.2011	06.01.2011
OIL	BMF	001.2011	13.01.2011
OIL	GM	001.2011	06.01.2011	567	0,12	0,52
OIL	GM	001.2011	13.01.2011	568	0,12	0,52
OIL	TM	001.2011	06.01.2011
OIL	TM	001.2011	13.01.2011
AMMONIA	FER	001.2011	06.01.2011	428	0,82	0,00	521
AMMONIA	FER	001.2011	13.01.2011	450	0,82	0,00	549
AMMONIA	FW	001.2011	06.01.2011	428	0,82	0,00	521
AMMONIA	FW	001.2011	13.01.2011	445	0,82	0,00	543
AMMONIA	BMF	001.2011	06.01.2011	428	0,82	0,00	521
AMMONIA	BMF	001.2011	13.01.2011	438	0,82	0,00	534
AMMONIA	GM	001.2011	06.01.2011
AMMONIA	GM	001.2011	13.01.2011
AMMONIA	TM	001.2011	06.01.2011	428	0,82	0,00	521
AMMONIA	TM	001.2011	13.01.2011	428	0,82	0,00	521

[Former Member]

I found the solution to my issue. thank You

[former_member586947]

Hi Jillu,

    Why have you gone for cell definition? Can you elaborate your required output a bit more detail?

Report should display only Oil and Ammonia products data?

Regards,

Satya

[Former Member]

Attached is the structure what I have

I have to calculate  the below  formula N DEDUCTION  for lets say 10 products . Product names don’t change.

N DEDUCTION for Oil = (Avg rate OIL –( %N OIL * USDN AMMONIA )) / %P OIL

N DEDUCTION for CRUDE OIL = (Avg rate CRUDE OIL –( %N CRUDE OIL * USDN AMMONIA )) / %P CRUDE OIL

Similarly for all products in rows.

USDN is nothing but  Average rate / %N  and makes sense to be calculated only for AMMONIA value

SO for all products we multiply the %N of the product with USDN AMMONIA value

I created cells thinking that I can pick up USDN AMMONIA value and calculate the final formula for each product.

Now the problem that I explained in my original message is that everything works fine except for product OIL which has values only for Publication = GM and USDN AMMONIA is not available at GM publication.

This calculation was done in excel earlier which I am trying to bring through BEx .

[former_member586947]

Hi Jillu,

    I didn't understand the requirement completely even now also.

How you achieved the calculation in excel? 

Regards,

Satya

[Former Member]

In excel, the users do a manual calculation with formula. They have the entire database in excel and the update values after each fiscal year period. So I m trying to automate one of many formula in BEX