Quickbooks, Pivot Table, Calculated Field mess

[lasusa]

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?

 

[Jerry Sullivan]

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.

 

[lasusa]

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]

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]

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]

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]

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]

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]

I see that I can format the numbers; but do you know of a way to format the cell alignment?

 

[Jerry Sullivan]

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"