Quickbooks, Pivot Table, Calculated Field mess

lasusa

New Member
Joined
Apr 10, 2013
Messages
16
Hi there -

I am trying to generate a report in Excel pulling data via ODBC from a Quickbooks file. All I want to do is subtract (2) source columns and show the difference in a column in the middle of my Pivot Report.

Any suggestions?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi and Welcome to the Board,

I haven't worked with connections to Quickbooks, but you should be able to create a calculated field.
It's not clear what you mean by "the middle of my Pivot Report"....Calculated fields need to be placed in the Data (or Values) area the Pivot Report, but there is no restriction on the position you place the Calculated Field within that area.

There are some scenarios in which Calculated Fields cannot be used, such as when also using Grouping.

Have you used Calculated Fields before with PivotTables that reference simple workbook ranges?
If so, what happens when you try to create a Calculated Field using the same process?

If you need some more help, please identify what version of Excel you are using.
 
Last edited:

lasusa

New Member
Joined
Apr 10, 2013
Messages
16
Hi -

I am using Excel 2010.

I want the report to have columns displayed in the following order:

Customer, Item, Quantity, Unit Amount, Total Amount

Quickbooks fails to provide a field for the open quantity remaining on an open order. Instead, I have to calculate by subtracting the order's original order quantity was minus the quantity invoiced. So Quantity is my calculated field; but I can't figure out how to add this column into the middle of my Pivot Table. I have tried adding a formula column to the Excel table that my Pivot Report pulls from; but on refresh, the formula does not carry over to the newly added cells.


Does this help?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Here are the steps you would typically take to add a calculated field. It assumes that your existing pivot table field list already contains two fields:
"Original Order Quantity" and "Quantity Invoiced". (modify the instructions to match your field names).

Click on one of your header caption cells in the PivotTable (like Unit Amount).
In the Ribbon > Options > Fields Items & Sets > Calculated Field...

In the Insert Calculated Field Dialog Box:
Name: Quantity
Formula: ='Original Order Quantity'- 'Quantity Invoiced'
(use your actual field names. It's easier and more accurate to pick them from the fields off the list instead of typing them).
Click the Add button
Click the OK button

This should place the Quantity field in the PivotTable. You can change its position in the PivotTable Field List pane.
 

lasusa

New Member
Joined
Apr 10, 2013
Messages
16

ADVERTISEMENT

Hi There -

Well, the steps that you gave me, still wouldn't let me arrange the columns the way I wanted to; however, it did occur to me to move all of my number fields to the values section of the table so that I could arrange everything in that section. And it totally works!

So, thank you for your help!
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Good to hear that worked. The value fields need to be to the right of the row labels, but you can rearrange the order within the values area.

Aside from having the desired order, you need to have the "numbers" in the values area if you are going to summarize them by sum, average, count, etc.- otherwise they are just text.
 

lasusa

New Member
Joined
Apr 10, 2013
Messages
16

ADVERTISEMENT

So I have learned. I just assumed wherever that the values area was only for custom fields. \

My next frustration is that, upon refresh, some of my data lose their alignment and I have to re-align and reassign currency symbols to the currency fields. I have a macro set up to do this; but didn't know if there was an easier step that I might be missing?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Apply the number formatting through the value field settings instead of applying it to the actual cells of the PivotTable.

To do that, Right-click on the field header in the PivotTable > Value Field Settings....
In the dialog that pops up, there should be "Number Format" button at the bottom next to OK and Cancel.
Use that to set the number format.

If you don't see a "Number Format" button, it means Excel doesn't consider the field numeric.
This can happen if you have a mix of numbers, text and blanks in your data field.
 

lasusa

New Member
Joined
Apr 10, 2013
Messages
16
I see that I can format the numbers; but do you know of a way to format the cell alignment?
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
As far as I know, you can't format cell alignment at the field level the same way as number format (although the default alignment of number format varies- Text=Left, Numbers=Right).

I haven't had any trouble retaining cell alignment in PivotTables, but I might have a different scenario than yours.

Typically I apply alignment to entire column ranges that include PivotTables, then override that alignment if needed for any cells above the Pivot.

You might also try under PivotTable Options:
Check "Preserve cell formatting on update"
Uncheck "AutoFit column widths on update"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,573
Members
414,389
Latest member
MarkElla

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
Top