pivot table filter by Row where either of 2 Column values <>0

[craigexcel]

I have multiple pivot tables (PT) in a workbook, which are all based on the same source data. Each pivot table is identical except for the unit acronym used in the Report Filter. I just noticed an issue with one of them, and have searched for a while now, without success. I'm certain one of you will be able to a different way to get to a solution. I update the pivot tables via VBA, but I'm pretty sure I can figure that part out if you can help with the 'how to' part.

Each PT has 1 Report Filter, 1 Column (Period, as in Mmm-yy), 2 Rows (GL Account, and Trading Partner acronym), and 1 Value (Sum of Amount). The Columns field actually reports 2 Periods: Dec-17 and Jun-18. I have also added a Calculated Item which subtracts Dec-17 values from Jun-18 values (which is labeled "variance").

The issue I found is this: I have applied a Value Filter to the 'Trading Partner' Row, where "Sum of Amount" <> 0. As I now see, this actually only applies to the "Sum of Amount" values for the Jun-18 field. However, if the Dec-17 field shows a value, but the Jun-18 fields does not, I still need for that Trading Partner acronym to display in the PT. So, ONLY if both Periods' values = 0 do I want that Trading Partner to be hidden in the PT.

Do you know a way to display any Trading Partner if EITHER Period, i.e. "OR") has a value <> 0?

Im using Office 365.

 

[sheetspread]

Try:

Excel 2010
	A	B	C	D
1	GL_ACCOUNT:0GL_ACCOUNT	TRAD_PART:0PCOMPANY	Period	AMOUNT_DC:0BALANCE
2	2090000	AA11	12/1	-8577429.19
3	2090000	AA11	6/1	-8878897.3
4	2090000	AA12	12/1	0
5	2090000	AA12	6/1	0
6	2090000	AA13	12/1	0
7	2090000	AA13	6/1	0
8	2090000	AA16	12/1	0
9	2090000	AA16	6/1	0
10	2090000	AA33	12/1	-472397.68
11	2090000	AA33	6/1	-293905.64
12	2090000	AA38	12/1	0
13	2090000	AA38	6/1	0
14	2090000	AA40	12/1	0
15	2090000	AA40	6/1	0
16	2090000	AACI	12/1	0
17	2090000	AACI	6/1	-2836.36
18	2090000	AADC	12/1	-37149.8
19	2090000	AADC	6/1	-21495.04
20	2090000	AAIB	12/1	-54788390.57
21	2090000	AAIB	6/1	-54089414.34
22	2090000	AATN	12/1	4408.32
23	2090000	AATN	6/1	0
24	2090000	AATR	12/1	0
25	2090000	AATR	6/1	0
26	2090000	DACH	12/1	0
27	2090000	DACH	6/1	0
28	6510000	AA11	12/1	1887034.42
29	6510000	AA11	6/1	1953357.44
30	6510000	AA12	12/1	0
31	6510000	AA12	6/1	0
32	6510000	AA13	12/1	0
33	6510000	AA13	6/1	0
34	6510000	AA16	12/1	0
35	6510000	AA16	6/1	0
36	6510000	AA33	12/1	163449.59
37	6510000	AA33	6/1	101691.35
38	6510000	AA38	12/1	0
39	6510000	AA38	6/1	0
40	6510000	AA40	12/1	0
41	6510000	AA40	6/1	0
42	6510000	AACI	12/1	0
43	6510000	AACI	6/1	468
44	6510000	AADC	12/1	13002.43
45	6510000	AADC	6/1	4513.96
46	6510000	AAIB	12/1	602672.38
47	6510000	AAIB	6/1	594983.46
48	6510000	AATN	12/1	-1102.08
49	6510000	AATN	6/1	0
50	6510000	AATR	12/1	0
51	6510000	AATR	6/1	0
52	6510000	DACH	12/1	0
53	6510000	DACH	6/1	0
54	8500990	AA11	12/1	8577429.19
55	8500990	AA11	6/1	8878897.3
56	8500990	AA12	12/1	0
57	8500990	AA12	6/1	0
58	8500990	AA13	12/1	0
59	8500990	AA13	6/1	0
60	8500990	AA16	12/1	0
61	8500990	AA16	6/1	0
62	8500990	AA33	12/1	472397.68
63	8500990	AA33	6/1	293905.64
64	8500990	AA38	12/1	0
65	8500990	AA38	6/1	0
66	8500990	AA40	12/1	0
67	8500990	AA40	6/1	0
68	8500990	AACI	12/1	0
69	8500990	AACI	6/1	2836.36
70	8500990	AADC	12/1	37149.8
71	8500990	AADC	6/1	21495.04
72	8500990	AAIB	12/1	54788390.57
73	8500990	AAIB	6/1	54089414.34
74	8500990	AATN	12/1	-4408.32
75	8500990	AATN	6/1	0
76	8500990	AATR	12/1	0
77	8500990	AATR	6/1	0
78	8500990	DACH	12/1	0
79	8500990	DACH	6/1	0
80	9900100	AA11	12/1	-1887034.42
81	9900100	AA11	6/1	-1953357.44
82	9900100	AA12	12/1	0
83	9900100	AA12	6/1	0
84	9900100	AA13	12/1	0
85	9900100	AA13	6/1	0
86	9900100	AA16	12/1	0
87	9900100	AA16	6/1	0
88	9900100	AA33	12/1	-163449.59
89	9900100	AA33	6/1	-101691.35
90	9900100	AA38	12/1	0
91	9900100	AA38	6/1	0
92	9900100	AA40	12/1	0
93	9900100	AA40	6/1	0
94	9900100	AACI	12/1	0
95	9900100	AACI	6/1	-468
96	9900100	AADC	12/1	-13002.43
97	9900100	AADC	6/1	-4513.96
98	9900100	AAIB	12/1	-602672.38
99	9900100	AAIB	6/1	-594983.46
100	9900100	AATN	12/1	1102.08
Pivot

Excel 2010
	A	B	C	D
101	9900100	AATN	6/1	0
102	9900100	AATR	12/1	0
103	9900100	AATR	6/1	0
104	9900100	DACH	12/1	0
105	9900100	DACH	6/1	0
Pivot

Sub hidepairsofzeroes()
Dim pt As PivotTable
Dim i As PivotItem
Dim j As PivotItem
Set pt = Sheets("Pivot").PivotTables(1)
For Each i In pt.PivotFields("GL_ACCOUNT:0GL_ACCOUNT").PivotItems
For Each j In pt.PivotFields("TRAD_PART:0PCOMPANY").PivotItems
On Error Resume Next
If pt.GetPivotData("AMOUNT_DC:0BALANCE", "GL_ACCOUNT:0GL_ACCOUNT", i.Name, "TRAD_PART:0PCOMPANY", j.Name, "Period", 43070) = 0 And pt.GetPivotData("AMOUNT_DC:0BALANCE", "GL_ACCOUNT:0GL_ACCOUNT", i.Name, "TRAD_PART:0PCOMPANY", j.Name, "Period", 43252) = 0 Then
j.Visible = False
End If
Next j
Next i
End Sub

Excel 2010
	F	G	H	I	J
1	Sum of AMOUNT_DC:0BALANCE		Period
2	GL_ACCOUNT:0GL_ACCOUNT	TRAD_PART:0PCOMPANY	12/1	6/1	Formula1
3	2090000	AA11	(8,577,429.19)	(8,878,897.30)	(301,468.11)
4	2090000	AA33	(472,397.68)	(293,905.64)	178,492.04
5	2090000	AACI	-	(2,836.36)	(2,836.36)
6	2090000	AADC	(37,149.80)	(21,495.04)	15,654.76
7	2090000	AAIB	(54,788,390.57)	(54,089,414.34)	698,976.23
8	2090000	AATN	4,408.32	-	(4,408.32)
9	2090000 Total		(63,870,958.92)	(63,286,548.68)	584,410.24
10	6510000	AA11	1,887,034.42	1,953,357.44	66,323.02
11	6510000	AA33	163,449.59	101,691.35	(61,758.24)
12	6510000	AACI	-	468.00	468.00
13	6510000	AADC	13,002.43	4,513.96	(8,488.47)
14	6510000	AAIB	602,672.38	594,983.46	(7,688.92)
15	6510000	AATN	(1,102.08)	-	1,102.08
16	6510000 Total		2,665,056.74	2,655,014.21	(10,042.53)
17	8500990	AA11	8,577,429.19	8,878,897.30	301,468.11
18	8500990	AA33	472,397.68	293,905.64	(178,492.04)
19	8500990	AACI	-	2,836.36	2,836.36
20	8500990	AADC	37,149.80	21,495.04	(15,654.76)
21	8500990	AAIB	54,788,390.57	54,089,414.34	(698,976.23)
22	8500990	AATN	(4,408.32)	-	4,408.32
23	8500990 Total		63,870,958.92	63,286,548.68	(584,410.24)
24	9900100	AA11	(1,887,034.42)	(1,953,357.44)	(66,323.02)
25	9900100	AA33	(163,449.59)	(101,691.35)	61,758.24
26	9900100	AACI	-	(468.00)	(468.00)
27	9900100	AADC	(13,002.43)	(4,513.96)	8,488.47
28	9900100	AAIB	(602,672.38)	(594,983.46)	7,688.92
29	9900100	AATN	1,102.08	-	(1,102.08)
30	9900100 Total		(2,665,056.74)	(2,655,014.21)	10,042.53
31	Grand Total		0.00	0.00	0.00
Pivot

Before setting the pivot table I changed the source data blanks to 0s. After running the code I added the variance calculated item.

 

[craigexcel]

Thanks again sheetspread for your suggestions on resolving this one. Just wanted to close the loop with what I ultimately ended up using. Following is the relevant part of the code that was at issue.

lMonth1 = CDbl(ws.Range("C6"))       'previous month's value
lMonth2 = CDbl(ws.Range("D6"))       'current month's value

        iLastRowPT = ws.Range("A" & Rows.Count).End(xlUp).Row - 1       'find last row of pivot table (excluding 'Grand Total' row)
        For Each i In pvt.PivotFields("GL_ACCOUNT:0GL_ACCOUNT").PivotItems
            For Each j In pvt.PivotFields("TRAD_PART:0PCOMPANY").PivotItems
            On Error Resume Next
                If pvt.GetPivotData("AMOUNT_DC:0BALANCE", "GL_ACCOUNT:0GL_ACCOUNT", i.Name, "TRAD_PART:0PCOMPANY", j.Name, "Period", lMonth1) = 0 And _
                    pvt.GetPivotData("AMOUNT_DC:0BALANCE", "GL_ACCOUNT:0GL_ACCOUNT", i.Name, "TRAD_PART:0PCOMPANY", j.Name, "Period", lMonth2) = 0 Then
                    j.Visible = False   'if both months' AMOUNT = 0, then hide row
                Else
                    j.Visible = True    'otherwise, leave row visible
                End If
            Next j
        Next i

Many thanks again for a great solution!

 

[sheetspread]