how to calculate keyfigure(CKF) values in workbook?

[Former Member]

hi,

i am generating YTD Report.

i have 2 key figures(std price, moving price), displaying in the query and put that query in WORKBOOK. after refreshing the workbook i need to display 2 calculated key figure values (variance , Variance %).

the std price, moving price values are displaying for each and every period and the calculated keyfigures displayed at the end.

below is report's preview

PERIOD 01 PERIOD 02 PERIOD 03

STD PRICE MOVING PRICE STD PRICE MOVING PRICE STD PRICE MOVING PRICE.

10 20 10 20 10 20

how to achieve this??

REGARDS

vadlamudi

[Former Member · Accepted solution]

hi,

am giving little bit idea to you , just follow the sequence u can get the result,

open query- go with columns area- create new structure,

make selections - selection1 ( Period 1 ) ,selection2(period2),selection3 (period3);

under selection1: period1 (restrict the time values)

Std price,

Moving price,

verienc,

% verience,

second selection2 ( Period 2 )

: std price

: moving price

: verience

: % verience

.

.

..

do like for third selection also , the report disply like this format

period1 period2 period 3

std price moving price verience % verience std price moving price verience % verience ,,, .......... /////

i think this is solve u r problem

[Former Member]

Hello Venu,

If I got the question right, you need to create two additional calculated columns in your workbook.

If this is the case I can help you with VBA code that would execute automatically upon the workbook refresh or after a button is pushed on the workbook.

Br,

Dan

[Former Member]

hi Dan,

cheers, could you plz provide me the Code for the extra 2 calculated values( variance, variance %).regards

vadlamudi

[Former Member]

Hello,

Hope this piece of code will help you. You still have to make some adjustments. Add this code in Excel via: Toold->Macro->Visual Basic Editor.

The additional function will convert the corresponding column number to a string. Excel programtically stores column number not letters. i.e. column AB will be stored and reffered to as column no 90 (just a blind example).

Please add the column numbers to variables XX and YY at the beginning of the code. Basically the code will do the following:

- determine the last used row

- determine the last used column

- insert two new columns to the right of the last used column together with descripition

- cycle from the first used row i (i=24 i.e. - put here the first row of the result set - the one immediately below column NAME) to the last used Rowwhich is, typically, containing the Overall Result key word and apply the Variance formula native to excel for one of the columns.

You have to add the code to calculate the Variance %. Let me know if the code works - you still have to do a couple of refining on the code in order for it to work smoothly.

Last but not least feel free to delete the part of the code which does not suite your needs. I've just pasted it from one of my workbooks without deleting unused variables etc.

 Sub SAPBEXonRefresh(queryID As String, resultArea As Range) 

 Dim xlWrkBk As Excel.Workbook 

 Dim wdth As Long 

 Dim ofset As Integer

 Dim startcell As Range

 Dim xlsht As Excel.Worksheet

 Set xlWrkBk = ThisWorkbook

 Set xlsht = xlWrkBk.Worksheets(1)

 Dim rngstr As String

 Dim rngx, rngy As String

 Dim j, primcol As Integer

 Dim check As Integer

 Dim lastcol, w, bdb, bcb, da, ca, sld, smn As Integer

 Dim XX, YY aas integer

 XX= u2018 PUT here the column number for Year 1

 YY= u2018 PUT here the column number for Year 2

u2018 identify last row

 lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

u2018 identify first used row from column C u2013 non filter column

 Dim q As Integer, z As Integer

 q = 1

 Do While q <= lastrow

 If Len(Sheet1.Cells(q, "C")) > 2 Then

 z = q

 q = lastrow

 End If

 q = q + 1

 Loop

 z = z u2013 1

u2018 identify last column

 lastcol = ActiveSheet.Cells(z, ActiveSheet.Columns.Count).End(xlToLeft).Column

 bdb=lastcol+1

 wdth = Sheet2.Columns("F:F").ColumnWidth

u2018 code to set the column width

 Sheet1.Columns(Number2Char(bdb - 1) & ":" & Number2Char(bdb - 1)).Select

 Sheet1.Columns(Number2Char(bdb - 1) & ":" & Number2Char(bdb - 1)).ColumnWidth = wdth

u2018 INSERT COLUMNS

 Sheet1.Cells(z, Number2Char(lastcol)).EntireColumn.Offset(0, 1).Insert

 Sheet1.Cells(z, Number2Char(lastcol + 1)) = "Variance"

 Sheet1.Cells(z + 1, Number2Char(lastcol + 1)) = "RON"

 Sheet1.Cells(z, Number2Char(lastcol + 2)).EntireColumn.Offset(0, 1).Insert

 Sheet1.Cells(z, Number2Char(lastcol + 2)) = "Variance %"

 Sheet1.Cells(z + 1, Number2Char(lastcol + 2)) = "%"

' INSERT CODE HERE TO CALCULATE THE VALUES IN THE COLUMNS

 primcol = 0

 i = 24 

u2018 replace I with a value that represents the start of the result set. You can determine this

u2018programmatically by looking at the first value in the column C or D.

 Dim strv, strw, strx, stro, strp, strq, strr As String

  strv = Number2Char(lastcol + 1)

  strx = Number2Char(lastcol + 2)

 Do While Sheet1.Cells(i, "A") <> "Overall Result"

 Sheet1.Cells(i, Number2Char(lastcol + 1)) = Evaluate (u201CVar(u201C &  Sheet1.cells(I,Number2char(XX) & u201C*u201D & Sheet1.cells(i, Number2char(yy) & u201C)u201D)

 i = i + 1

 Loop

 End Sub

Function Number2Char(ByVal vNumber As Integer)

Dim iDiv As Double, iMod As Integer

If vNumber < 1 Then Exit Function

iDiv = vNumber

While iDiv > 26

iMod = iDiv Mod 26

If iMod = 0 Then

iMod = 26

iDiv = iDiv - 1

End If

Number2Char = Chr(64 + iMod) & Number2Char

iDiv = iDiv \ 26

Wend

Number2Char = Chr(64 + iDiv) & Number2Char

End Function

Regards,

Dan

Edited by: Dan on Sep 21, 2009 2:38 PM

Edited by: Dan on Sep 21, 2009 2:42 PM

Edited by: Dan on Sep 21, 2009 2:43 PM

[Former Member]

Hi Venu,

Good that you found a work around (with the help of a workbook) to solve this problem of yours. let me help u out.

You can get exactly what you need but would take a bit of efforts.

First of all, in the query of yours, Select the Period in the keyFigures section, Now, create a structure with two keyfigures in it (std price, moving price). Now create a new query with only variance and variance % as the keyfigures.

execute the first query, save it as a workbook, insert the second query in it.

Now in a new sheet in this workbook, Map the values from both these query results.

I told you it was tedious, but this is the only way around as far as I see.

Let me know if you have any questions about manually mapping in the workbook.

regards,

Sree.

Edited by: Sree Nair on Sep 21, 2009 9:56 AM

[former_member387970]

a structure key figure std price\move price drill down period.