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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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

M
  • Question
Sum of field in pivot table
Replies
0
Views
142
mabubakerali
M
B
  • Question
Calculated Field Coming Back as Zero
Replies
4
Views
277
biotbravo
B
A
  • Solved
Calculated Field
Replies
5
Views
123
Alex Blakenburg
A
B
  • Question
Calculated Field in pivot table difficulty
Replies
2
Views
276
bluefish44
B
gravanoc
  • Question
Power Query & VBA Issue
Replies
1
Views
308
GraH
GraH

Forum statistics

Threads
1,214,639
Messages
6,120,679
Members
448,977
Latest member
dbonilla0331

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