Help with Calculated Field/Pivot Table

[CTKC]

Basically I would like to create a calculated field based on certain pivotfields. I run a weekly report for each fiscal month with a plan and actual column. As weeks progress the source data grows. For example 1/4 Plan, 1/4 Actuals, 1/11 Plan, 11/Actuals, etc so the data source columns grow. The below code is what I have tried but I get an error. I have tried numerous other ways but to no avail. If anyone could help it would be appreciated.

dim pt as pivottable

set pt=activesheet.pivottables("pivottable1")

fields=pt.pivotfields.count

for i= 15 to fields
     if mid(pt.pivotfields(i).name,11,7)="Actuals" then

[bold]***** pt.calculatedfields.add "delta", "=pt.pivotfields(I).name & "-" & pt.pivotfields(i+1).name  """[/bold]

     end if
next

end sub

i've also tried.


pt.calculatedfields.add "delta", "=pt.pivotfields(I).name- pt.pivotfields(i+1).name & """

pt.calculatedfields.add "delta", "='pt.pivotfields(I).name'- 'pt.pivotfields(i+1).name' & """

pt.calculatedfields.add "delta", "='pt.pivotfields(I).name' & "-" & 'pt.pivotfields(i+1).name' & """

Thanks again.

 

[SkipVought]

Hi,

"...so the data source columns grow"

Why are you shooting yourself in the foot? This statement indicates that you have a poorly designed table and process. Please read this article and understand why you need to redesign your process.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

I surmise that you ought to revise how you get the new data into your source table. Where is the data coming from and how are you now getting that data into your table?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

The data is in an excel worksheet. THe first 14 columns are "standard" weekly fields but after that it depends on the weekending date.For example, right now, what I call my data file (just has a single tab with all the raw data), has 14 columns plus a column for each; 1/4 actuals, 1/4 plan, 1/11 plan, 1/11 actuals. Next week there will be two additional columns, 1/18 plan and 1/18 actuals.

The additional fields are always weekending Actuals then Plan. My idea was to look through the pivotfields and once it finds "Actuals" then create a calc field with plan-actuals.

Data is coming from SAP and the financial tool we use. I am getting the data into my data file by running a macro thatn formats each file and adds the information i need.

You are a tough one to please haha.

 

[SkipVought]

"THe first 14 columns are "standard" weekly fields"

What does that mean. Please remember that we have no idea what your data looks like of what question(s) your process is attempting to answer. What are "standard" weekly fields? Does that mean that you have headings in this table that refer to different weeks? If so, SAME BIG PROBLEM.

Your data, in order to be able to use the PivotTable Wisard effectively, or any other data analysis or data reporting feature in Excel, ought not to have columns that get added from report to report!!! If you continue this design, you will be fighting to do tasks that, with a properly designed table, would take your mere SECONDS or MINUTES to
complete.

You might consider a table design that look something like this...
[tt]
...... StartOfWeekDate | Plan/Act | Amount
[/tt]
When data comes in from SAP, append to rows at the bottom of your table. Use REAL DATES, not an abbreviation.

With a table design like this, it is unlikely that you will need ANY calculated fields in your PT.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

I apologize if I am not explaining my situation well enough because it is hard to type what my data looks like. I do use real dates, such as 1/04/2009 Plan as a heading. I was just abbreviating in my message. I would upload a sample of my data but I know you do not like when people do that (at least I assume). If you would like I will upload a sample so it is easier to explain.

Data from SAP includes the weekly labor charges from individuals for each specific account, which for example 20 individuals could charge. We do not plan individuals into our financial tool for each account rather a specific level code. So the data from the financial tool comes out by account with the amount of hours planned. From SAP i recieve the individuals names, in the fiancial tool I do not get that information so I created a field (column) that inputs the name of the Plan as "Plan, Plan". From the financial tool I just receive the account once with the total hours planned for the month. With SAP, I could get 20-30 people in the data file.

Previously I had a Plan/Actual column that labeled the values as such (actuals or plan) and a Date Column. Maybe I do not know the feature in Excel to create a delta based on that or just making this harder than it needs to be.



Is it possible to create a calculated item from how I am going about this. If i remove the pt.pivotfields(i).name and actually type the name of the ptfield it works, but it seems with the variable it doesn't. I am self taught and haven't been using VBA for a long time.

 

[SkipVought]

    pt.CalculatedFields.Add "delta", pt.PivotFields(i).Name & " - " & pt.PivotFields(i + 1).Name

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

With the code posted I was receiving this error:

Run-time error '-2147024882:

The formula you typed contains an error.

etc,etc...

After thinking about what I could be doing wrong and figured it out. It always seems to be something very trivial. The code below is correct and works properly (if you care). I had to add the single apostrophes (sp?)to the formula.

Another question, why do we not have to do the equal sign at the beginning of the formula ("=)?

pt.calculatedfields.add "delta", "'" & pt.pivotfields(i).name & "'" & "-'" & pt.pivotfields(i+1).name & "'"

 

[SkipVought]

Sorry, it should be...

pt.calculatedfields.add "delta", "=" & pt.pivotfields(i).name & "-" & pt.pivotfields(i+1).name

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Skip,

The code that I posted above works without putting a "=" in there. It also works if I include a "=" & .. Not sure why it works without a = though.

pt.calculatedfields.add "delta", "=" & pt.pivotfields(i).name & "-" & pt.pivotfields(i+1).name 
[/code

That still gives an error about an formula error, etc. 

Thanks for all your help Skip.

 

[SkipVought]

Funny, it works in my PT???

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[CTKC]

Do you have 2007? I am working on Excel 2003.

 

[SkipVought]

I have done it in both

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    pt.CalculatedFields.Add "Calc", "=Amt1+Amt2"
    pt.CalculatedFields.Add "Calc2", "=" & pt.PivotFields("Amt1") & "*" & pt.PivotFields("Amt2")
    pt.CalculatedFields.Add "Calc3", "=" & pt.PivotFields(2) & "-" & pt.PivotFields(3)

My source...
[tt]
Name Amt1 Amt2
a 1 22
w 2 33
e 3 44
r 4 55
t 5 66
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}