Formula calculation in BEx

[Former Member]

I have a report:

Item//Per1//Per2//Per3

income//25//36//52

costs//21//23//09

adj costs//4//13//8

Now I need a row like the below:

LTM adj costs//25//19//11 (25=4138 i.e. adding all the adj costs from present to last 2 periods) similarly 19 and 11 assuming the previous period adj costs values are 2 and 5.

NOTE: Items are from the structures in the rows and periods are offsets for Posting periods in the column.

How can I calculate or define the LTM adj costs.

Please let me know if I am clear.

Thanks,

Nikki

[Former Member · Accepted solution]

I think you have tu use two hide column wit per -q and -2; than you can use 'Define cells' function to insert the right formulas for every column.

Hope it help you.

Regards

[Former Member · Accepted solution]

Paola,

thanks for the response, I just gave an example. I have trailing 12 months and I need to insert values in the adj costs for the current period (totalling all the values from period 1 to 12 till date) and for the current period -1, I need to have a total of all the values from current period -1 to last 12 periods.

Hope I am clear.

Nikki

[Former Member · Accepted solution]

It's only for adj cost or for all the line?

[Former Member · Accepted solution]

Hi Nikki,

Even for 12 months I would still suggest to have cell definitions ...It is the simplest way you can do this with 2 structures in your query..

Ashish..

[Former Member · Accepted solution]

Hello Ashish,

I just have one structure in the row with FS Items. I am not sure of these cell definitions, can you direct me to some place where I can get more help.

Thanks,

Nikki

[Former Member · Accepted solution]

Hi Nikki,

You will have to create one more structure for the 12 periods..so that you will have 2 structures in the query.

Then you can define exception cells..

Here is the link to the Cell Definitions Explanation..

http://help.sap.com/saphelp_nw04/helpdata/en/f5/1bac3a22f0785ce10000000a11402f/frameset.htm

I have copied the content and pasted it below..

Defining Exception Cells
Use
When you define selection criteria and formulas for structural components and there are two structural components of a query, generic cell definitions are created at the intersection of the structural components that determine the values to be presented in the cell.

Cell-specific definitions allow you to define explicit formulas, along with implicit cell definition, and selection conditions for cells and in this way, to override implicitly created cell values. This function allows you to design much more detailed queries.

In addition, you can define cells that have no direct relationship to the structural components. These cells are not displayed and serve as containers for help selections or help formulas.

Prerequisites
The query contains two structures. You can define exception cells only for queries which have two structures. You can use the one structure for characteristic values and key figures for the other. You can then define cells if these prerequisites have been met.



A cell is the intersection between two structural components. The term Cell for the function Defining Exception Cells should not be confused with the term Cell in MS Excel. The formulas or selection conditions that you define for a cell always take effect at the intersection between two structural components. If a drilldown characteristic has two different characteristic values, the cell definition always takes effect at the intersection between the characteristic value and the key figure.

Procedure
In the Query Designer toolbar, choose Define Cells... to reach the cell definition functions.

New selection
...

1. Select a cell in the Cell Definition dialog box and, from the context menu, choose New Selection.

2. Define your selection by dragging a key figure and the characteristic values by which you want to restrict the key figure (using Drag&Drop) to the right-hand area of the New Selection dialog box. See also Defining Selections

You can use characteristic value variables instead of fixed characteristic values.

- If you want to change an existing variable, select this variable and, from the context menu, choose Edit. The SAP BW Variables Editor appears. For more information, see Changing Variables in the Variable Editor.

- If you want to define a new variable, select the characteristic value and, from the context menu, choose New Variable. The SAP BW Variables Wizard appears. For more information, see Defining Variables with the Variable Wizard.

3. Enter a description for the selection.



You can use Text Variables in the description.

4. Choose OK. The cell contains the description of the selection. The symbol shows you that a selection exists for this exception cell.

New formula
...

1. Highlight a cell in the Cell Definition dialog box and, using the secondary mouse button, choose New Formula from the context menu.

2. Define your formula using the operands and functions available. See also Defining Formulas

3. Enter a description of the formula.



You can use Text Variables in the description.

4. Choose OK. The cell contains the description of the formula. The symbol shows you that a formula exists for this exception cell.

New cell reference
You can use any cell that you define in a formula. If you want to reuse the value from the implicit, generic cell definition that is automatically created at the intersection of the two structural components of a query, select this cell in the Cell Definition dialog box and, using the secondary mouse button, choose New Cell Reference. A description taken from the two structural components now automatically appears in the cell. The symbol shows you that a cell reference exists for this exception cell. You can now reference to this cell. That is, you can reuse the implicit cell definition and do not have to create these values manually in a new selection.

Functions of explicitly defined cells:
Select a defined cell and choose the required function from the context menu (secondary mouse button).

· Edit

· Delete

· Where-Used List

Before you delete a cell, you can check for the formulas in which the defined cell is used in the where-used list.

· Properties

Choose Properties if you:

- Want to change the description of the cell.

- Want to change the highlighting of the cell.

- Want to hide the cell. That is, if you do not want to see the values of the cell.

Help Cells
Choose Help Cells, if you require additional cells for help selections or help formulas. You can use the functions New Selection and New Formula in the context menu to define help cells that are not displayed in the query to serve only as objects for help selections and help formulas.



For example, you can define an invoice in a help cell and use this in a “real” cell to calculate a duplicated invoice.

...

a. Right-click on the empty cell next to Help Cells to get to a context menu. From here, choose New Selection.

b. Use Drag & Drop to drag the Invoice key figure into the right-hand area of the New Selection dialog box.

c. Enter a description.

d. Choose OK. The cell contains the description of the selection. The symbol shows you that a selection exists for this exception cell.

e. From the context menu for the required cell of the query, now choose New Formula.

f. Drag the cell you have just defined, which you can find in the Operands window, under Cells, into the Formula window using Drag & Drop. Click on the button twice.

g. Enter a description.

h. Choose OK. The cell contains the description of the formula. The symbol shows you that a formula exists for this exception cell.

In the help cells that are already defined in the context menu, you can:

· Edit the cell definition

· Delete the cell definition

· Display the where-used list for the cell

· Change the description of the cell under Properties.

Result
You have defined one or more exception cells for a query. In the status bar of the Query Designer, the symbol shows you that the query contains exception cells.

[Former Member · Accepted solution]

Thanks a lot for all the information. But my question still remains. Example I have 6 periods displayed in my report.

Item/////Jan//Feb//Mar//Apr//May//June

Required Y1//Y2//Y3//Y4//Y5//Y6

Y1=JanFebMarAprMay+Jun

Y2=DecJanFebMarApr+May

Y3=NovDecJanFebMar+Apr

......

So on and so forth.

Problem is my Posting periods are defined in structure format but with offsets.

Any idea??

Nikki

[Former Member · Accepted solution]

Hi Nikki,

I believe you are displaying the individual values for every month in your report...

If you have this you just need to put a formula in the cell to add up all these values..

If I did not understand your req correctly..Pls let me know..

Good Luck

Ashish..

[Former Member · Accepted solution]

Hello Ashish,

I have all the periods displayed in my report but I am defining it in the form a structure i.e.

Posting period restricting between offsets (posting period -12 to posting period 0). So basically I can't take individual values into the formula and if I take the whole posting period, its giving the same values but how do I calculate the values which are not displayed.

I can actually email me my report ( do have your id )

Thanks,

Nikki

[Former Member · Accepted solution]

Hi Nikki..

Sure...e-mail me the report definition...I can have a look at it..

Hope I can find some way of doing it..Will give it a shot..

Ashish..

[Former Member · Accepted solution]

Hi Nikki,

Looks like you solved your problem..Can you share the solution..

Ashish..

[former_member188975 · Accepted solution]

Hi Ashish...I thought you solved it!

[Former Member · Accepted solution]

No Bhanu..

I had just given her ideas in the Forum..

If that solved it GREAT!!!!

I expected it to be solved ..

but nothing via personal E-mails..

your the woMAN Bhanu...

Ashish..

[Former Member]

Sorry... could you explain your answer..... I have similar problem...but can't understand what you did

I have 2 key figures in this period that I have to divide by a key figure from the next period.

Thank you.