Hide zeros in pivot graph

[monaldo12]

Hi,

I have a pivot which contains values with percentages, the source data contains formulas with zero outcomes, this will be updated automatically with every period new data arrives. So these zeros will always be in the source data.
Now I'm trying to make a graph of the pivot but it shows zeros and I would like to hide them in a line graph.
How can I make the orange line stop at week 11 and not show the zeros? Workaround with formulas besides the pivot is also fine.

 

[BSALV]

in your source for your pivottable, make the values with normally zero outcome an error-value with na()

Map1
	A	B
1	a	b
2	0	0,121145
3	0	0,670711
4	0	0,608777
5	1	0,51245
6	1	0,69425
7	1	0,350889
8	1	0,514961
9	1	0,415283
10	2	0,347898
11	2	0,826864
12	2	0,974547
13	2	0,168085
14	2	0,836149
15	3	0,084408
16	3	0,554619
17	3	0,920399
18	3	0,496366
19	3	0,240393
20	4	#N/B
21	4	#N/B
22	4	#N/B
23	4	#N/B
24	4	#N/B
25	5	#N/B
26	5	#N/B
27	5	#N/B
28	5	#N/B
29	5	#N/B
Blad1

Cell Formulas
Range	Formula
A2:A29	A2	=TRUNC(ROW()/5)
B2:B29	B2	=IF(A2<4,RAND(),NA())

 

[monaldo12]

in your source for your pivottable, make the values with normally zero outcome an error-value with na()

Map1
	A	B
1	a	b
2	0	0,121145
3	0	0,670711
4	0	0,608777
5	1	0,51245
6	1	0,69425
7	1	0,350889
8	1	0,514961
9	1	0,415283
10	2	0,347898
11	2	0,826864
12	2	0,974547
13	2	0,168085
14	2	0,836149
15	3	0,084408
16	3	0,554619
17	3	0,920399
18	3	0,496366
19	3	0,240393
20	4	#N/B
21	4	#N/B
22	4	#N/B
23	4	#N/B
24	4	#N/B
25	5	#N/B
26	5	#N/B
27	5	#N/B
28	5	#N/B
29	5	#N/B
Blad1

Cell Formulas
Range	Formula
A2:A29	A2	=TRUNC(ROW()/5)
B2:B29	B2	=IF(A2<4,RAND(),NA())

Don't think I understand it correctly and doubt if it's what I'm searching for. My source data is like this.
This contains a formula which basically looks into a different file and returns the values. Empty cells are shown as zeros in the input file.

 

[Marius44]

Hi
Will you be so kind to show the formula used in column B? Thanks.

Hi,
Mario

 

[BSALV]

suppose A are the values in another file, you check if there is a number in that other file, if not the result is na()

Map1
	A	B	C	D
1	other workbook	------>	actual formula	improved formula
2	2		2	2
3	3		3	3
4	4		4	4
5	5		5	5
6	6		6	6
7	7		7	7
8	8		8	8
9	9		9	9
10			0	#N/B
11			0	#N/B
12			0	#N/B
13			0	#N/B
14			0	#N/B
15			0	#N/B
16			0	#N/B
17			0	#N/B
18			0	#N/B
19			0	#N/B
Blad3

Cell Formulas
Range	Formula
C2:C19	C2	=+A2
D2:D19	D2	=IF(ISNUMBER(A2),A2,NA())
A2:A9	A2	=ROW()

 

[monaldo12]

suppose A are the values in another file, you check if there is a number in that other file, if not the result is na()

Map1
	A	B	C	D
1	other workbook	------>	actual formula	improved formula
2	2		2	2
3	3		3	3
4	4		4	4
5	5		5	5
6	6		6	6
7	7		7	7
8	8		8	8
9	9		9	9
10			0	#N/B
11			0	#N/B
12			0	#N/B
13			0	#N/B
14			0	#N/B
15			0	#N/B
16			0	#N/B
17			0	#N/B
18			0	#N/B
19			0	#N/B
Blad3

Cell Formulas
Range	Formula
C2:C19	C2	=+A2
D2:D19	D2	=IF(ISNUMBER(A2),A2,NA())
A2:A9	A2	=ROW()

Thanks a lot, this fixed my problem!

 

[BSALV]

you're welcome.
I see now you sum the values per week.
i didn't check but instead of na(), you can also use "-" (or another a character or string) and then you use the sum even for an incomplete week in your pivottable