Range Sum & Above function working wrong when supress zero values option cheked

[#1]

Dear All,

we want to add above sum of rows in pivot table so the expression is written as

if(Region=23, RangeSum(above(TOTAL column(1),1,22)), Data)

it's coming correct when suppress zero values is un-checked for Region = 23 as 158

when we check suppress zero values the result  coming wrong for region = 23 as 256

is it bug or please help if any other solution available.

attached qvw file for your reference.

Region	qty
1	1
2	2
3	-
4	-
5	5
6	6
7	-
8	-
9	-
10	10
11	11
12	12
13	13
14	-
15	-
16	-
17	17
18	18
19	-
20	20
21	21
22	22
23	158
24	24
25	25

Region	qty
1	1
2	2
5	5
6	6
10	10
11	11
12	12
13	13
17	17
18	18
20	20
21	21
22	22
23	256
24	24
25	25

Thanks in advance.

[maxgro]

maybe

if(Region=23, RangeSum(above( sum(qty),1,rowno()-1)), sum(qty))

[MK_QSL]

use this....

if(Region=23,RangeSum(above(TOTAL SUM(Data),1,RowNo())),Data)

[johnw]

I'm unclear why it isn't working, but it seems to be confused by the use of column(1) once you get to a certain point farther up in the chart. But if you instead use Data (or I'd guess whatever expression you use when not 23), it works.

if(Region=23,rangesum(above(Data,1,22)),Data)

I don't know if that will work for your real data model and chart, but perhaps it would.

[sunny_talwar]

I think no matter how hard you try, it will be difficult for you to use Column() function to achieve what you are looking to get. I did try to steer you towards use of variables? Did it not pan out for you or did you never even give it a shot?

above rows total problem in pivot table

[#6]

used but same problem

[MK_QSL]

I am sure anyone from these answers would work. But if you are trying these solutions in real data which are too different from the sample provided, no one can help you further without getting more information.

When you said,, used but same problem... what you mean to say? Can you please provide some more information..?

[#8]

Dear All

please forget about my real development just i tried with simple excel data by reloading and designed pivot table

by writing simple expression as below by adding dimension as Region

if(Region=23, RangeSum(above(TOTAL column(1),1,22)), Data)

or

if(Region=23, RangeSum(above(TOTAL column(1),1, ROwNo()-1)), Data)

its giving correct total when properties -- > suppress zero values un-check

its giving wrong total when properties -- > suppress zero values checked

request you people to try the same then you will understand the problem

actually unfortunately i am unable to attach actual QVW file due to am not able to view @attache option in my browser.

Excel Data As Follows :

Region	Data
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25

Thanks ....

[#9]

Dear All

please forget about my real development just i tried with simple excel data by reloading and designed pivot table

by writing simple expression as below by adding dimension as Region

if(Region=23, RangeSum(above(TOTAL column(1),1,22)), Data)

or

if(Region=23, RangeSum(above(TOTAL column(1),1, ROwNo()-1)), Data)

its giving correct total when properties -- > suppress zero values un-check

its giving wrong total when properties -- > suppress zero values checked

request you people to try the same then you will understand the problem

actually unfortunately i am unable to attach actual QVW file due to am not able to view @attache option in my browser.

Excel Data As Follows :

Region	Data
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25

Thanks ....

[MK_QSL]

as already replied... you can't use Column(1) here.. use SUM(Data) instead..