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]

Can you add another calculated item for dec-17 + jun-18 ?

 

[craigexcel]

Thanks for your reply sheetspread. I just did a quick addition to a pivot table, but I can't see how that moves me further along. I still would only be able to Filter Values, based on the Trading Partner 'Row', of the "Sum of Amount". I still can't sort by values from both Periods. Am I missing something?

 

[sheetspread]

If the sum is 0 and there are no negatives, then all of the months must be 0. Can't you filter that way?

 

[craigexcel]

Not exactly. The "variance" (Calculated Item) may not always be 0, nor would it always be positive. The Dec-17 and Jun-18 "Period" values could be either, and therefore, the "variance" could be either positive or negative. In one of the instances where this issue comes into play, the Dec-17 value = 4000, while the Jun-18 value = 0, which returns a "variance" of (4000). I still want that to appear in the pivot table. The "Filter Value" doesn't show the "variance" Calculated Item as an item to filter on, so that's where I'm stuck.

 

[sheetspread]

Here are some lines I wrote without seeing your date grouping, etc. If they don't help we can try something else:

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Date	Number
2	Q	December	87		Sum of Number	Column Labels
3	Q	June	0		Row Labels	December	June	Formula1
4	Q	December	0		E		0	0
5	Q	June	87		Q	87	87	0
6	Q	December	0		R	96	95	-1
7	W	June	52		T	83	152	69
8	W	December	0		U	0	0	0
9	E	June	0		W	0	52	52
10	R	December	6		Y	69		-69
11	R	June	95
12	R	December	90
13	R	June	0
14	R	December	0
15	T	June	72
16	T	December	0
17	T	June	42
18	T	December	83
19	T	June	38
20	Y	December	69
21	U	June	0
22	U	December	0
23	U	June	0
Pivot

Sub hidepairsofzeroes()
Dim pt As PivotTable
Dim i As PivotItem
Set pt = Sheets("Pivot").PivotTables(1)
For Each i In pt.PivotFields("Name").PivotItems
If pt.GetPivotData("Number", "Name", i.Name, "Date", "June") = 0 And pt.GetPivotData("Number", "Name", i.Name, "Date", "December") = 0 Then
i.Visible = False
End If
Next i
End Sub

Excel 2010
	E	F	G	H
2	Sum of Number	Column Labels
3	Row Labels	December	June	Formula1
4	Q	87	87	0
5	R	96	95	-1
6	T	83	152	69
7	W	0	52	52
8	Y	69		-69
Pivot

 

[craigexcel]

Many many thanks sheetspread! The results of your ad hoc spreadsheet and pivot table are exactly the outcome I'm looking for! Perfect interpretation. Now I just need to incorporate your code into the existing code, and that should do it. Another view from a different angle always helps get it done. Thanks!

 

[craigexcel]

So, getting the VBA right is proving to be a bit problematic. Here's what I have so far, in the If statement that compares the "Dec-17" ("Period") column to the "Jun-18" ("Period") column. (These Period values are formatted this way in the source table, and in the pivot table. Both use "1" as the day, but it's just not displayed.)

In the following line, the editor indicates a ")" is missing, but I can't see where. I think I have seen, and have read, before, that sometimes the error description isn't necessarily accurate. Is that what's missing? Or, do I need some additional double quotes when writing this as code, instead of a formula? Or, something else? Also, I took a guess that I could reference the DATE value in the way I did, but don't know if that's accurate.
[C6] is where "Dec-17" is located (in the pivot table).
[D6] is where "Jun-18" is located (in the pivot table).

    If pt.GetPivotData("AMOUNT_DC:0BALANCE","$A$5","Period",DATE(YEAR(ws.Range("C6")),MONTH(ws.Range("C6")),DAY(ws.Range("C6"))), "GL_ACCOUNT:0GL_ACCOUNT",2090000,"TRAD_PART:0PCOMPANY", i.Name) = 0 & _
        And pt.GetPivotData("AMOUNT_DC:0BALANCE","$A$5","Period",DATE(YEAR(ws.Range("D6")),MONTH(ws.Range("D6")),DAY(ws.Range("D6"))), "GL_ACCOUNT:0GL_ACCOUNT",2090000,"TRAD_PART:0PCOMPANY", i.Name) = 0 Then

Also, I just realized, since there will always be multiple (4) "GL_ACCOUNT:0GL_ACCOUNT" numbers, including 2090000, that will need to be dynamic too. Pivot table shows "GL_ACCOUNT:0GL_ACCOUNT" as the 1st Row in Column A, followed by "TRAD_PART:0PCOMPANY" as the 2nd Row. in Column B. The pivot table is formatted to display "GL_ACCOUNT:0GL_ACCOUNT" in each row of the pivot table, to facilitate LOOKUPS elswhere in the worksheet. So the pivot table displays as: x rows of 1st G/L account number, followed by related "TRAD_PART:0PCOMPANY" acronyms, followed by "Dec-17" "AMOUNT_DC:0BALANCE" values, followed by "Jun-18" "AMOUNT_DC:0BALANCE" values, followed by the "variance" column.

Any thoughts for the proper syntax??

 

[sheetspread]

Can you post a sample file?

 

[craigexcel]

Here's a typical pivot table from the workbook. The "AATN" Trading Partner is the issue here, since each GL Account has a value for Dec-17, but none for Jun-18. My current methodology 'hides' these, since filtering by 'sum of amount...' only takes into account the Jun-18 values.

Book1
	A	B	C	D	E
3	ASSIGNMENT:0ALLOC_NMBR	AADO
4
5	Sum of AMOUNT_DC:0BALANCE		Period
6	GL_ACCOUNT:0GL_ACCOUNT	TRAD_PART:0PCOMPANY	Dec-17	Jun-18	variance
7	2090000	AA11	(8,577,429.19)	(8,878,897.30)	(301,468.11)
8	2090000	AA12			0.00
9	2090000	AA13			0.00
10	2090000	AA16			0.00
11	2090000	AA33	(472,397.68)	(293,905.64)	178,492.04
12	2090000	AA38			0.00
13	2090000	AA40			0.00
14	2090000	AACI		(2,836.36)	(2,836.36)
15	2090000	AADC	(37,149.80)	(21,495.04)	15,654.76
16	2090000	AAIB	(54,788,390.57)	(54,089,414.34)	698,976.23
17	2090000	AATN	4,408.32		(4,408.32)
18	2090000	AATR			0.00
19	2090000	DACH			0.00
20	2090000 Total		(63,870,958.92)	(63,286,548.68)	584,410.24
21	6510000	AA11	1,887,034.42	1,953,357.44	66,323.02
22	6510000	AA12			0.00
23	6510000	AA13			0.00
24	6510000	AA16			0.00
25	6510000	AA33	163,449.59	101,691.35	(61,758.24)
26	6510000	AA38			0.00
27	6510000	AA40			0.00
28	6510000	AACI		468.00	468.00
29	6510000	AADC	13,002.43	4,513.96	(8,488.47)
30	6510000	AAIB	602,672.38	594,983.46	(7,688.92)
31	6510000	AATN	(1,102.08)		1,102.08
32	6510000	AATR			0.00
33	6510000	DACH			0.00
34	6510000 Total		2,665,056.74	2,655,014.21	(10,042.53)
35	8500990	AA11	8,577,429.19	8,878,897.30	301,468.11
36	8500990	AA12			0.00
37	8500990	AA13			0.00
38	8500990	AA16			0.00
39	8500990	AA33	472,397.68	293,905.64	(178,492.04)
40	8500990	AA38			0.00
41	8500990	AA40			0.00
42	8500990	AACI		2,836.36	2,836.36
43	8500990	AADC	37,149.80	21,495.04	(15,654.76)
44	8500990	AAIB	54,788,390.57	54,089,414.34	(698,976.23)
45	8500990	AATN	(4,408.32)		4,408.32
46	8500990	AATR			0.00
47	8500990	DACH			0.00
48	8500990 Total		63,870,958.92	63,286,548.68	(584,410.24)
49	9900100	AA11	(1,887,034.42)	(1,953,357.44)	(66,323.02)
50	9900100	AA12			0.00
51	9900100	AA13			0.00
52	9900100	AA16			0.00
53	9900100	AA33	(163,449.59)	(101,691.35)	61,758.24
54	9900100	AA38			0.00
55	9900100	AA40			0.00
56	9900100	AACI		(468.00)	(468.00)
57	9900100	AADC	(13,002.43)	(4,513.96)	8,488.47
58	9900100	AAIB	(602,672.38)	(594,983.46)	7,688.92
59	9900100	AATN	1,102.08		(1,102.08)
60	9900100	AATR			0.00
61	9900100	DACH			0.00
62	9900100 Total		(2,665,056.74)	(2,655,014.21)	10,042.53
63	Grand Total		0.00	0.00	0.00
pivot AADC

Cell Formulas
Range	Formula
A3	ASSIGNMENT:0ALLOC_NMBR
A5	Sum of AMOUNT_DC:0BALANCE
A6	GL_ACCOUNT:0GL_ACCOUNT
A7	2090000
A8	2090000
A9	2090000
A10	2090000
A11	2090000
A12	2090000
A13	2090000
A14	2090000
A15	2090000
A16	2090000
A17	2090000
A18	2090000
A19	2090000
A20	2090000 Total
A21	6510000
A22	6510000
A23	6510000
A24	6510000
A25	6510000
A26	6510000
A27	6510000
A28	6510000
A29	6510000
A30	6510000
A31	6510000
A32	6510000
A33	6510000
A34	6510000 Total
A35	8500990
A36	8500990
A37	8500990
A38	8500990
A39	8500990
A40	8500990
A41	8500990
A42	8500990
A43	8500990
A44	8500990
A45	8500990
A46	8500990
A47	8500990
A48	8500990 Total
A49	9900100
A50	9900100
A51	9900100
A52	9900100
A53	9900100
A54	9900100
A55	9900100
A56	9900100
A57	9900100
A58	9900100
A59	9900100
A60	9900100
A61	9900100
A62	9900100 Total
A63	Grand Total
B3	AADO
B6	TRAD_PART:0PCOMPANY
B7	AA11
B8	AA12
B9	AA13
B10	AA16
B11	AA33
B12	AA38
B13	AA40
B14	AACI
B15	AADC
B16	AAIB
B17	AATN
B18	AATR
B19	DACH
B21	AA11
B22	AA12
B23	AA13
B24	AA16
B25	AA33
B26	AA38
B27	AA40
B28	AACI
B29	AADC
B30	AAIB
B31	AATN
B32	AATR
B33	DACH
B35	AA11
B36	AA12
B37	AA13
B38	AA16
B39	AA33
B40	AA38
B41	AA40
B42	AACI
B43	AADC
B44	AAIB
B45	AATN
B46	AATR
B47	DACH
B49	AA11
B50	AA12
B51	AA13
B52	AA16
B53	AA33
B54	AA38
B55	AA40
B56	AACI
B57	AADC
B58	AAIB
B59	AATN
B60	AATR
B61	DACH
C5	Period
C6	43070
C7	-8577429.19
C11	-472397.68
C15	-37149.8
C16	-54788390.57
C17	4408.32
C20	-63870958.92
C21	1887034.42
C25	163449.59
C29	13002.43
C30	602672.38
C31	-1102.08
C34	2665056.74
C35	8577429.19
C39	472397.68
C43	37149.8
C44	54788390.57
C45	-4408.32
C48	63870958.92
C49	-1887034.42
C53	-163449.59
C57	-13002.43
C58	-602672.38
C59	1102.08
C62	-2665056.74
C63	1.00535544333979E-08
D6	43252
D7	-8878897.3
D11	-293905.64
D14	-2836.36
D15	-21495.04
D16	-54089414.34
D20	-63286548.68
D21	1953357.44
D25	101691.35
D28	468
D29	4513.96
D30	594983.46
D34	2655014.21
D35	8878897.3
D39	293905.64
D42	2836.36
D43	21495.04
D44	54089414.34
D48	63286548.68
D49	-1953357.44
D53	-101691.35
D56	-468
D57	-4513.96
D58	-594983.46
D62	-2655014.21
D63	3.49245965480804E-10
E6	variance
E7	=Period[-1]-Period[-2]
E8	=Period[-1]-Period[-2]
E9	=Period[-1]-Period[-2]
E10	=Period[-1]-Period[-2]
E11	=Period[-1]-Period[-2]
E12	=Period[-1]-Period[-2]
E13	=Period[-1]-Period[-2]
E14	=Period[-1]-Period[-2]
E15	=Period[-1]-Period[-2]
E16	=Period[-1]-Period[-2]
E17	=Period[-1]-Period[-2]
E18	=Period[-1]-Period[-2]
E19	=Period[-1]-Period[-2]
E20	584410.239999958
E21	=Period[-1]-Period[-2]
E22	=Period[-1]-Period[-2]
E23	=Period[-1]-Period[-2]
E24	=Period[-1]-Period[-2]
E25	=Period[-1]-Period[-2]
E26	=Period[-1]-Period[-2]
E27	=Period[-1]-Period[-2]
E28	=Period[-1]-Period[-2]
E29	=Period[-1]-Period[-2]
E30	=Period[-1]-Period[-2]
E31	=Period[-1]-Period[-2]
E32	=Period[-1]-Period[-2]
E33	=Period[-1]-Period[-2]
E34	-10042.5299999993
E35	=Period[-1]-Period[-2]
E36	=Period[-1]-Period[-2]
E37	=Period[-1]-Period[-2]
E38	=Period[-1]-Period[-2]
E39	=Period[-1]-Period[-2]
E40	=Period[-1]-Period[-2]
E41	=Period[-1]-Period[-2]
E42	=Period[-1]-Period[-2]
E43	=Period[-1]-Period[-2]
E44	=Period[-1]-Period[-2]
E45	=Period[-1]-Period[-2]
E46	=Period[-1]-Period[-2]
E47	=Period[-1]-Period[-2]
E48	-584410.239999958
E49	=Period[-1]-Period[-2]
E50	=Period[-1]-Period[-2]
E51	=Period[-1]-Period[-2]
E52	=Period[-1]-Period[-2]
E53	=Period[-1]-Period[-2]
E54	=Period[-1]-Period[-2]
E55	=Period[-1]-Period[-2]
E56	=Period[-1]-Period[-2]
E57	=Period[-1]-Period[-2]
E58	=Period[-1]-Period[-2]
E59	=Period[-1]-Period[-2]
E60	=Period[-1]-Period[-2]
E61	=Period[-1]-Period[-2]
E62	10042.5299999993
E63	6.00266503170133E-11