Calculations Excel based

[Former Member]

Hello Experts,

I need to execute multiplications and divisions between columns in my Layout.

Do you know a way to do this without FOX?

I know we can add columns in Excel In Place, and execute the calculations locally, but the values loaded to this columns, are no written to the database. I really need them, to be written.

Before starting to develop FOX formulas, I would like your precious opinion about this issue.

Thanks in advance.

João Arvanas.

[Former Member · Accepted solution]

Hi Again,

I think Danny's suggestion of using variables was based on the assumption that the users needed to supply multipliers/divisors for the calculations that were not already contained in key figures. So that instead of hard coding a constant multiplier into the fox calculation the users could 'dynamically' set the multipler (perhaps a projected growth factor in a planning model) to be applied just by altering the value of the numeric variable (a variable not basde on a characteristic).

However, my understanding based no your original description is that your calculations are only amongst existing key figures - for example a price times a quantity or things of that nature where you have all the source information already in the records and just want to calculate addtional key figures to store the results - in which case variables may not be relevant to you r needs, and simlpe fox formula should be sufficient.

[Former Member]

I will go for fox in this scenario.

Thanks

Ravi Thothadri

RThothadrii@yahoo.com

[Former Member]

Joao,

Can you give a practical example of what you need to achieve? Qtr total? Qtr% of total year or ???

Also do you need to store the number to be able to pick it up and use it for further calculation in FOX? usually you only add additional KF if it need to be stored for later calculation where as you just need the info, you tend to do excel formula in the layout specification and later do the Calculated key figure for in BEx reports.

Mary

[Former Member]

Mary,

I’m developing the costcenter Planning scenario. My problem is that I have different types of planning, being them differentiated by nature.

I’m building a scenario for an insurance company. They have a planning scenario with 20 or more layouts and each layout is a planning level.

For example, the values added in the first layout are used in the second for calculations and so on. So when I’m planning in one layout I have to make automatic calculations in the dependent layouts without having to run them. I’m doing this using FOX formulas.

According to Andrew and Danny this is the best approach. What do you think of this?

Best regards,

João Arvanas

[Former Member]

Fonseca,

I have excel calculation in my layout and while saving, as I execute the planning functions that will do the same calculations to save it.

Priya

[Former Member]

Fonseca,

Based on your requirement, I would agree with Andrew that it is better to use FOX instead of excel based calculations.With FOX you can use variables, calculate the values store them in a special period and use them later on for further planning in subsequent dependent layouts.

Hope this helps.

Cheers

Srini

[Former Member]

Hello People,

I would like to thank you all for your help.

I will apply your sugestions.

Best Regards.

João Arvanas

[Former Member]

Hi Joao / Monica,

Is the insurance company giving you a existing cost center planning model in an excel template / workbook ? Can some of the layouts they give you be commonized to similar levels of details or a more limited level of details by driving it with variables? What type of calculations are they doing?

I would look in more detail what they are trying to accomplish and see if there are things I can do to facilitate their goals and process with data modelling and BW as well as the FOX formulas. Excel formulas in layout you need things to be in the same planning layout or you would need to store them and pick it up later.

Hope this helps,

Mary

[Former Member]

Fonseca,

As Srini and Andrew have highlighted above, u shud be able to get this functionality up and running. But there are other significant factors u need to consider before u do this. With Web-based planning being the sort-after functionality and better user interface, I would do not this. Simple Fox formulae should be able to do what you are trying to do and also you can use numeric variable to provide more dynamic values employed for multiplication/divisions.

This is just my 2 cents.

Danny

[Former Member]

Hello Danny,

At the present moment the layouts are going to run on SAP GUI through Planning Folders, but I’m certain that, in a near future the requirements will change and the situation will evolve to Web GUI.

According to this cenario I will follow your suggestion, but could you explain in more detail what you meant with “numeric variable to provide more dynamic values employed for multiplication/divisions”.

I have worked with dynamic columns in the past for rolling forecast, but I’m not sure if I understood your point in this particular case, ie, the combination between FOX and dynamic values.

Thank you for your help,

Mónica Fonseca

[Former Member]

of course Srini meant to say 'non SAP Column' not row, which means in your layout config you would need to insert columns where you want to calculate and usually display the totals/derived values, you will also need to hide the SAP data columns where the calculated data will be copied and saved

(You could do this the other way around, ie display the SAP data columns and hide the non SAP data columns where your formula are, but then you wouldn't see the result until you saved)

Since you need to hide columns yuo will need to make sure you de-select the 'auto column width' feature, which is revealed as a checkbox in a pop up window from one of the pushbuttons on the excel layout config screen.

If you do not disable this feature you will not be able to hide columns.

You will also need to activate the SAP macro through another simiar checkbox/pop up wondow.

You can record the macro to copy data from one column to another in excel then copy the code from the default 'macro1' into the body of the sap delivered macro.

This whole solution is only viable however if you are building your user interface with planning folders, as macro functionality for excel layouts is not availble in the web.

[Former Member]

Fonseca,

This can be accoplished in a two step process. First step is use your excel to calculate the result (using multiplications and divisions) in a separate non-SAP row

where you would write excel formula to arrive at the result.Second step is to write a simple macro that copies the result into the SAP data column with the right key figure.Yo would need put this macro in one of the SAP delivered macros SAPAFTERDATAPUT or SAPBEFOREDATAGET and activating them.

Hope this helps.

Cheers

Srini