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

need the total result of existing KFs

In query1, I have two KFs: K1 - "total qty" and K2 -"average stck". I need to get a "turnover" KF, the formular is

"turnover" = "total qty" / "average stck".

I need to use the RESULT of the above two KFs -K1 and K2, but not the each individual row of these two KFs.

for example: if the user put oct/2005 to 11/2005 at the begining of the query, then query result now is like:

K1 K2

oct 10 15

Nov 8 25

RESULT 18 40

so my new KF "turnover" is based on the total RESULT of K1 and K2: eg K1 =18, K2 =40(but not on the individual month).

so the "turnover" = 18/40

i don't mind to create aother query to get this done if it is necessay(replacement with query), but just really don't know now what is the good way...

Please advice,

Ping

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 19, 2006 at 10:09 AM

    HI Ping,

    If you are ready to save this query as a Workbook the following solution works.

    Save the Query as a Workbook. Write an Excel formula in the column "I" like

    For Eg if Your qty is in D Column and Stock in H Column then

    =IF($A9="Result",$D9/$H9,0) and save the work book.

    For more Formatting you can use SAPBEXonRefresh event where you can write VBA code and do the formatting.

    Regs

    Gopi.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Ping,

      You have mistaken. I have just give an example. If you copy the formula to the whole column then it will become dynamic.Try this.

      The following code I have written which will even make the result column with the same format BW result formats.Please paste it in your VBA Code Module.

      Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

      Dim iStart As Integer

      Dim iEnd As Integer

      Dim sRange As String

      Dim iRow As Integer

      iRow = 1

      'Find the Start Row and End Row

      Do While True

      sRange = "H" & iRow

      If iStart = 0 And Range(sRange).Value <> "" Then

      iStart = iRow

      iEnd = 0

      End If

      If Trim(Range(sRange).Value) = "" Then

      iEnd = iRow

      End If

      If iStart <> 0 And iEnd <> 0 Then

      Exit Do

      End If

      iRow = iRow + 1

      Loop

      'Initialise the result Column Formats to Blank

      Range("J:J").Copy

      Range("I:I").PasteSpecial (xlPasteFormats)

      'Filling the result column with the formula

      sRange = "I" & iStart & ":" & "I" & iEnd

      Range(sRange).FormulaR1C1 = "=IF(RC1=""Result"",RC4/RC8,0)"

      Range("I1").Select

      'Copy the format of the result to the new column

      sRange = "H" & iStart & ":" & "H" & iEnd

      Range(sRange).Copy

      sRange = "I" & iStart & ":" & "I" & iEnd

      Range(sRange).PasteSpecial (xlPasteFormats)

      End Sub

      Here I have assumed that

      Column 1 - Where the Text "Result" appears

      Column 4 - Your Numerator

      Column 8 - Your Denominator

      Column I is the Result Column.

      Column H - I am using to find the rows of BW Result and BW Result Format.Column H Should not contain any Blank data.In your case you can select an equivalent column.

      Column J - I used to initialise the format of I ie that is to make color and data everything blank.

      Please change the Column Names according to your requirement.

      If you want I can send the Sample Excel File also.

      Regs

      Gopi.

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.