need the total result of existing KFs

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Thank you for your input.

But the excel formular can't work dynamaic, as the row of the result of D and H column willnot awlays row 9, right?

could anyone let me know in more details how can I make this done in VBA?

[Former Member · Accepted solution]

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.