Some Excel Fields Not Showing Up In Pivot Table Pull Downs
Aug 16, 2012
I have been having issues with some excel fields not showing up in my pivot table pull downs.
For example I entered the date 6/8/97 into a field in my database and after I hit refresh on my pivot table that specific date doesn't show up in the Date field list pull down. It is like the pivot table doesn't even see that date and/or it is recognizing it as another date. I have this same issue w/ other field lists (eg. last names).
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
My problem is this: I use Excel 2010 and have Pivot Tables that are using a Data Connection which is being built (and rebuilt) with VBA. The Pivot Table field is calculating a field that contains an integer value. The Pivot Table field filter properly displays this value.
However, the Pivot Table itself calculated Sum field only displays 0 (aka zero). Other fields that are exactly the same as this one are displaying properly.
My data connection is built as follows:
Code:
ThisWorkbook.Connections.Add "ChartQuery", "", "OLEDB;Persist Security Info=0;DSN=Excel Files; DBQ=" & wbFullName & _ ";DefaultDir=" & wbPath & ";DriverId=1046;MaxBufferSize=1024;PageTimeout=5; BackgroundQuery:=False", sSQL, 2 (And yes, my sSQL is sound, there are no typos, no special character issues)
The filter in the Pivot Table shows my value (for example, 14) however it always only displays a zero
My Pivot Table is formatting the field (like the others just like it) as a Number, two decimals My Pivot Table data source is ChartQuery
The worksheet this is pulling from is also set to use Number format for the entire column, but changing that doesn't make any difference even on the other fields that are working.
When you double click on data in a pivot table this brings up the drill down info. Is it possible for this to appear in a new workbook rather than the active workbook?
Any help if grately appreciated. If you need more information please post what you require and i will reply.
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X) end if"
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;
Sub RemoveGhostPivotItems() Dim ghost As PivotItem Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.ManualUpdate = True For Count = 1 To 10 On Error Resume Next For Each ghost In pt.PivotFields(Count).PivotItems ghost.Delete Next ghost Next Count pt.ManualUpdate = False End Sub
My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
I have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?
When I make a pivot table I wanted to know why it is showing duplicate names. Here is an example workbook below. It should group all the same names together. However it isn't doing so. Anyway around this problem?
I was wondering if it were at all possible to add multiple columns to the values portion of a pivot table at once, instead of individually dragging. When you click on them, it auto-sends them to Row Labels, which I don't want. I have a bunch of colums I want to add to Values and don't want to waste a bunch of time dragging them individually.
My data has a field for "Donor Type." There are about 2 dozen types of and about half of them start with the letters CF. When I do a pivot table, instead of getting 2 dozen rows of data I'd like all the CF donors to be in the same row. Is there a way to do that without changing my data? Some sort of filter or grouping within the pivot table?
I have fields "Day", "Posts", "Impressions" in a pivot table and I need to create a new metric for the average Impressions per Post. Ideally, this new metric should function just like the other fields in the pivot table, and not just static, because I'd like to break it out not just by Day, but also Time, etc.
Day Count of Posts Sum of Impressions Impressions Per Post
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?
Dim ws As Worksheet Set ws = Excel.Sheets("Sheet1") On Error Goto err: If ws. Range("IV1").Value = "dog" Then ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden Else ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden End If
i have a pivot table that shows the customer names. when i select the customer drop down box i can all the customer i want to see. but in the table itself there are some customer not showing. this is the first time this has happened
Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.
The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..
The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.
I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
I have pivot tables in diff pages im trying to make a button to reset all the fields in all the pivot tables..
i tried recording a macro to do this but it only works for page fields.. it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....
I am trying to use a summary field in the pivot table in a new calculated filed, but am unable to do so. Let me first describe the context so its easier to understand:
I have a collection of customer purchase records, which look like the following:
Customer Name, Customer ID, Purchase Amount, Activation Date ------------------------------------------------------------- John , 100 , $150 , 2011-04-01 17:07:50.0 John , 100 , $250 , 2011-04-01 17:07:50.0 Paul , 101 , $125 , 2011-08-20 11:10:27.0
I have several 1000 records like the above and I need to create a summary report which looks like:
Customer Name, Total purchased, Activation date, Avg monthly purchase ----------------------------------------------------------- John , $350 , 2011-04-01 , $175
The average monthly purhcase needs to be calculated based on the date of report generation. So in the case above, the average is calculated as of 2011-06-01.
In order to generate the report above, I created a pivot table with "Customer Name" in the "row labels" section and "Pruchase amount" and "Activation date" in the "values" section of the pivot table. When I try to calculate the "Avg Monthly Purchase", I'm running into the following problems:
1. The activation date is not being displayed as a date, but instead shows 0, when I set the value field settings to "Min"
2. I tried to create the "Avg Monthly Purchase" as a Calculated Field and then use the "Sum of Purchase Amount" field that the pivot table calculates. However, I'm unable to reference the "Sum of Purchase Amount" field in the calculated field.
I have a pivot table listing different company names in the first column under 'row labels' and there are calculated fields, a count and an average in columns B and C respectively. Is it possible to insert another field in column D that calculates the difference between values in column B and C (even though they are calculated fields themselves)?
Worksheet A contains two columns, that maps individual operations to their unitary cost: COLUMN 1 = a list of operations COLUMN 2 = the cost associated with each operation.
I can do a vlookup on this worksheet to retrieve the cost of each individual operation.
On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.
operation 1 was performed 5 times operation 2 was performed 7 times operation 3 was performed 4 times
I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be = operation * vlookup("name of operation","range for lookup table",2,0)
... unfortunately, "references, names and arrays are not supported in pivottable formulas".
I have a Pivot table that pulls the Avg of two fields for two months, see example below.
Avg Gross $ Avg Net $ Jan 2014 20 10 Feb 2014 30 20
sample 1.png See sample attached.
The Avg Gross and Net is shown by going into the values and selecting "Summarized value by -> Average".
On the right side of this Pivot, what I wanted to do is to show a Avg Gross to Net $ in this pivot. So the formula should take "Avg Gross $" - "Avg Net $" = Avg Gross to Net $.
I am having trouble calculting this new field in the pivot table using a calculated field because the Calculated field pulls the variables from the existing field list and there isn't a field called "Avg Gross/Net"....I need to find a way to calculate the Avg Gross to Net into the Pivot table so I can pull a pivot graph out of it.
