Pivot Table Calculated Field - Null Values Treated as Zero

M

mamboq

Board Regular
Joined
Nov 15, 2008
Messages
67
I have an Excel pivot table using an Access table as the external data source. In the Access table a particular numeric field (Field1) has many null values.

In the pivot table, I have added a calculated field called "Upper" with the formula being Field1 + Field2. If the Field1 value is null, I want the Upper value to be null, but at the moment the pivot table seems to treat the null values as zero, and a value is displayed. For example, if Field1 is null, and Field2 is 320, I want the calculated field to return null, but it is returning 320.

I've tried changing the calculated field formula to: IF(Field1="","",Field1+Field2)

But the pivot table still sees the null value as zero (not "") and returns a value.

Is there any way to make the pivot table return null (or an error) in the 'Upper' calculated field if Field1 in the Access table is null?

And does anyone know why the pivot table treats Field1's null values as zero when used in the formula of a calculated field, but as null when actually brought into the pivot table as a data field?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Anyone have any ideas?

This problem is driving me crazy, and now I've found that the same happens when data is missing. For example, if 2009 has products A & B, but 2008 does not, when filtering by year and grouping by product, the calculated field treats the 'missing' data as zero. That is, the 'Upper' calculated field (Field1+Field2) returns zero for products A & B when data is filtered for 2008, when it should return null (because no data exists for 2008 Products A & B).

Is there any way for a pivottable calculated field to determine if a field is null, or the record doesn't exist, and return null in the pivottable if either of these is true?
 
Upvote 0
Hi,

Instead of using the pivot table's calculated field, create the 'Upper' field via SQL. So edit the current SQL to add the extra field as below.

Code: 
SELECT Iif(Field1 Is Null, Null, Field1 + Field2) AS [Upper]

I don't know the easiest way for you to edit the SQL.

Maybe via the VBE? Such as, if you go from the sheet with the pivot table to the VBE's immediate window, type in
? activesheet.pivottables(1).pivotcache.sql

If that returns the SQL, just edit it and then write it back by editing the above line to become,
activesheet.pivottables(1).pivotcache.sql="SELECT ...."

(Then refresh the pivot table.)

HTH, Fazza
 
Upvote 0
Thankyou so much Fazza . I didn't realise PivotCache had an SQL property - up until now I had been using the SourceData property to change the pivot's data from Preliminary to Final. The SQL property seems so much simpler.

Just one issue with using the SQL property - the database containing the data for the pivot is password-protected. Is it possible to set the SQL property without requiring the user to input the password - perhaps by specifying the password in the code somehow?
 
Upvote 0
I don't know, sorry. I don't work with Access or passwords & don't know about that. Maybe you can put the password in the connection string? Suggest you search via google, and also search this forum: and maybe start a new thread?

Another thought, if you create a new pivot table from the Access source whilst using the macro recorder, the created VBA should indicate how the password is handled. Just duplicate that in your file.
 
Upvote 0
No worries....

I ended up placing a 'flag' field in the Access table instead - if the Field1 value is not null, the value in the new field 'Field1 Null?' is set to -1. Then in my pivot table calculated field, I changed the formula to: IF('Field1 Null?'=-1,Field1+Field2,"") which seems to be working. That way the pivot table doesn't have to requery the data each time the calculated field changes (which could happen multiple times during a user's session).

Thanks again for your help and suggestions... I'll definitely make a note of the pivotcache's SQL property for future use.

Tim
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel Pivot-Table calculated fields with if statement return wrong results
Replies
3
Views
309
RoryA
RoryA
M
  • Question Question
Sum of field in pivot table
Replies
0
Views
316
mabubakerali
M
J
  • Question Question
pivot table pull value if name changes
Replies
1
Views
176
Kaper
Kaper
J
  • Question Question
How can I copy the grouping rules from one pivot table to another pivot table?
Replies
0
Views
298
jaza_tom
J
B
  • Question Question
Calculated Field Coming Back as Zero
Replies
4
Views
511
biotbravo
B

Forum statistics

Threads
1,223,922
Messages
6,175,382
Members
452,639
Latest member
RMH2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top