Logic for reporting

[Former Member]

Experts,

I need your help in achiving the following in my query.

Scenario:

I have SALES, UNITS, PH2 and CALMONTH coming from the Cube.

CALMONTH PH2 SALES UNIT

10/2008 0110 20 10

10/2008 0110 30 10

Result(By PH2) 50 20

10/2008 0111 35 25

10/2008 0111 30 10

Result(By PH2) 65 35

What I need to Achieve is divide total SALES / total UNITS by PH2 and save it in another column as below

CALMONTH PH2 SALES UNIT AVG

10/2008 0110 20 10 2.5 (50/20)

10/2008 0110 30 10 2.5

Result(By PH2) 50 20 2.5

10/2008 0111 35 25 1.871(65/35)

10/2008 0111 30 10 1.871

Result(By PH2) 65 35 1.871

Can you please give some inputs on how I can achieve this.

Thanks

Raj

[Former Member · Accepted solution]

Hi Raj,

You can create a new formula as

SUMCT(sales)/SUMCT(unit) ,

And u should have result display for PH2 it should work.

Regards,

Rathy

[Former Member · Accepted solution]

Thanks everyone for valuable inputs.

Rathy,

Thats exactly what I ended up doing, used SUMCT. Thanks for validating my thinking. However, Is there a way to lock in this value and make it not to change in the navigation phase of the report>?

Thanks

Raj

[Former Member · Accepted solution]

Hi Raj,

Good to know it helped.Have u can tried the same with SUMRT and SUMGT. Not sure whether it will give the desired result.

Regards,

Rathy

[Former Member · Accepted solution]

Rathy,

Yes I have tried with that, but SUMCT gives me the desired result. My challenge now is to lock in the value after the report is executed first time...I have opened another thread for that question.

Thanks a lot for taking to the time to help me out.

Thanks

Raj

[Former Member]

Hi,

You can try this one out.

Create a new formula Column1 defined as follows:

Sales/Unit.

Create another new formula Column2 defined as follows:

%CT Column1 (percentage share of result obtained from Percentage Function).

Create formula AVG defined as follows:

Column1/Column2.

Go to properties of Column1 and Column2 and hide them.

Regards,

Anjana.

[andrea_previati]

YOu can achieve this with a calculated KEY figure but this key figure will calculate also on all the data and not only on sums

If you want to calculate only on sums you should create another KF in your cube and populate it with an ABAP routine making a select sum and dividing.

Hope it helps

Andreas