Skip to Content
author's profile photo Former Member
Former Member

Calculations Excel based

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 19, 2007 at 02:57 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2007 at 09:28 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2007 at 10:56 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 17, 2007 at 02:18 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 18, 2007 at 12:47 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 26, 2007 at 01:51 PM

    I will go for fox in this scenario.

    Thanks

    Ravi Thothadri

    RThothadrii@yahoo.com

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.