Calculated items

[noviceexcel]

Hi,

I have a pivot table with a calculated item. The problem is that it shows data for all rows EVEN when there is no data there. So the pivot table is a lot longer that it needs to be. How do I get the pivot table to show only rows with data in it? Also, all of the fields DO NOT have checks beside "Show Items With No Data". Any help would be greatly appreciated. Thanks

 

[noviceexcel]

Maybe I didn't explain my self very well in my previous post. I am using Excel 2003 and find after creating a calculated items that it is listing all rows including those with no data. In fact, it is not looking at the row fields properly. It is listing all data even if there is no data in the data set. Can anyone pls help me? Thanks

 

[xenou]

It seems possible that the calculated item must calculate for all rows, in order to determine the result. I think it would calculate based on the source data. Anyway, you seem to have already discovered by trial and error what happens here - unfortunately, not what you hoped for.

Pivot tables are in their nature hard to explain and hard to give instructions for - they allow for a lot of flexibility and have many options. If you want more help, I'd suggest you post the source data and describe the results you want in the pivot table. Generally speaking, the layout of pivot tables is their biggest weakness - the more fields you add, the more awkward they become. In my opinion they are for analysis, not for reports.

AB

 

[noviceexcel]

You will notice in the pivot data below it is listing Australia even though there is no data. Can this be fixed/

Month/Yr 1 Data
5/1/2007
Region Country Type Summary Sum of NET PRICE Sum of Units / Qty Sum of Avg Price
LA Argentina 3D
HoseHog
Large
New Products
Other
parts
PowerRake $36,720 1 $36,720
Recourse
rent
S-100
STS132
Used
XD $38,440 1 $38,440
(blank)
Australia 3D
HoseHog
Large
New Products
Other
parts
PowerRake
Recourse
rent
S-100
STS132
Used
XD
(blank)

 

[xenou]

I honestly don't think so...You could probably hide Australia "by hand" by right-clicking the region and deselecting the checkbox for australia. But this would hide it permanently, not just when there is no data.

If you can post your source data (not the pivot table data) maybe we can figure something out...also what your desired fields are for the final report. Is this a set of data that you want to "pivot" a lot or are you just using pivot tables for convenience to summarize the data?

AB

 

[noviceexcel]

I have posted some of the column headings for my data. Basically, each row contains sales data on a specific date. I added calculated field and then a calculated item as well. I saw this problem elsewhere on Mr.Excel but no solution was ever provided. I have checked more than 8 books on Excel and scoured google.com but can't the answer.

Month Country Region Net Sales Qty

 

[xenou]

To be honest, pivot table questions, as far as I can tell, often go unanswered/unsolved. There something about them that doesn't go well with easy answers - either they work or they don't. If you'd like to work on some alternatives, post your source data in more detail (column headers and data in those columns). Generally, you can do a lot with SQL-style queries or normal Excel formulas IF your data is clean (not mixing up text and numbers in the same column, or text and dates in the same column, and not too many blank rows and other extraneous stuff - just a plain old grid of data, that's what works best).

AB

 

[PaddyD]

In general, method for excluding unwanted records from a pivot is as follows:

1) add an extra column to the source data to act as a flag
2) Define your include / exclude condition in a formula that evaluates to T / F
3) add this extra column in the page field of your pivot & set accordingly

 

[noviceexcel]

I have atttached some source data but it looks messy. I am not sure
how to format to make it look nicer. Can you pls give me more details on the 3 steps you suggested. Thanks

Invoice Sales Orer # Month/Yr 1 Customer Region US states Revised Territory Territory Country dir/DLR Model Type Summary Serial # Units / Qty "Total
List Price" Discount Disc% NET PRICE Cost GROSS MARGIN G.M. % Territory Mgr Lar/Ch/Pwr/Oth State Cust. Type Ship Date
I72189 S17351 Jan-08 Commerial Concrete, MI NA MI East MW USA dir SXP14 Large 30330-0108 1 $288,940 ($13,621) 4.7% $275,319 $116,084 $159,234 58% D. Mors
I71844 S17265 Jan-08 Osburn Contractors, TX NA TX Midwest MW USA dir SXP14 Large 30331-0108 1 $288,940 ($11,558) 4.0% $277,382 $116,084 $161,298 58% J. Roberts
I72176 S17294 Jan-08 Gebbens Concrete, MI NA MI East NE USA dir SXP14 Large 30332-0108 1 $289,290 $0 0.0% $289,290 $116,084 $173,206 60% D. Mors
I72034 S17340 Jan-08 Warrens, FL w/Trade-in SXP @ $132,000 NA FL Midwest SE USA dir SXP14 Large 30333-0108 1 $294,243 $0 0.0% $294,243 $116,084 $178,159 61% S. Thompson

 

[PaddyD]

post a better view of your data:

http://www.mrexcel.com/forum/showthread.php?t=89356

But in general, say I;ve got a list of towns (col a) & their sales (col b). Towns with not sale have a 0 & I wan tto exclude them from the pivot.

In my new column, I'd use a formula like:

=SUMIF($A$2:$A$10,A2,$B$2:$B$10)>0

...to flag the source data records T / F if the sum for that town is > 0. Then, I'd add this new column as a page field, set it to TRUE - voila, only records that match my criteria are in the pivot.