PIVOT TABLES: Show correct count value based on different cell values

[JayB0730]

Hello,

I have a pivot table that currently shows the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EXPIRED[/TD]
[TD][/TD]
[TD]COMING UP TO EXPIRATION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of Item Code[/TD]
[TD]Sum of Total Value[/TD]
[TD]Count of Item Code[/TD]
[TD]Sum of Total Value[/TD]
[/TR]
[TR]
[TD]Tables[/TD]
[TD]74[/TD]
[TD]$817,008[/TD]
[TD]27[/TD]
[TD]$68,000[/TD]
[/TR]
[TR]
[TD]Chairs[/TD]
[TD]3[/TD]
[TD]$422[/TD]
[TD]19[/TD]
[TD]$900,000[/TD]
[/TR]
[TR]
[TD]Books[/TD]
[TD]18[/TD]
[TD]$254,000[/TD]
[TD]20[/TD]
[TD]$143,291[/TD]
[/TR]
</tbody>[/TABLE]


However, the issue I am having is that I don't want the "COUNT OF ITEM CODES" to include a count where the field in data source, "Quantity on Hand" = 0. While the SUM of Total Value would not be affected by this filter, I would want COUNT OF ITEM CODE to reflect correctly...see below:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EXPIRED[/TD]
[TD][/TD]
[TD]COMING UP TO EXPIRATION[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Count of Item Code[/TD]
[TD]Sum of Total Value[/TD]
[TD]Count of Item Code[/TD]
[TD]Sum of Total Value[/TD]
[/TR]
[TR]
[TD]Tables[/TD]
[TD]50[/TD]
[TD]$817,008[/TD]
[TD]27[/TD]
[TD]$68,000[/TD]
[/TR]
[TR]
[TD]Chairs[/TD]
[TD]1[/TD]
[TD]$422[/TD]
[TD]16[/TD]
[TD]$900,000[/TD]
[/TR]
[TR]
[TD]Books[/TD]
[TD]17[/TD]
[TD]$254,000[/TD]
[TD]20[/TD]
[TD]$143,291[/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated!

Thanks,
Jay

 

[Fazza]

hello, Jay

Seeing the sample source data would help understanding.

I may be on the 'wrong track' but it sounds to me like excluding data with 'Quantity on Hand' = 0 is what you're after.
If so, can you put the 'Quantity on Hand' field as a page field and deselect the quantity 0?

HTH

 

[JayB0730]

Hi Fazza,

Wow, I think I had the biggest "brain fart" known to man. Yes, how I completely missed the fact that I could simply throw the Quantity on Hand field into the reports filter section & 'uncheck' 0, is beyond me. Thank you for giving me the virtual "slap on the back of the head".

Thank you & take care,
Jay

 

[Fazza]

Just a minor BF, Jay. Suggest you double-check the solution does exactly what you want. All the best, Fazza