topic Re: Range Sum & Above function working wrong when supress zero values option cheked in QlikView

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

Fri, 30 Dec 2016 22:21:15 GMT

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.

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

maxgro — Fri, 30 Dec 2016 22:41:29 GMT

maybe

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

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

MK_QSL — Fri, 30 Dec 2016 22:44:40 GMT

use this....

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

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

johnw — Fri, 30 Dec 2016 22:45:30 GMT

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.

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

sunny_talwar — Sat, 31 Dec 2016 01:54:44 GMT

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

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

Sat, 31 Dec 2016 10:34:35 GMT

used but same problem

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

MK_QSL — Sat, 31 Dec 2016 10:50:55 GMT

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..?

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

Sat, 31 Dec 2016 11:28:18 GMT

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)

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 ....

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

Sat, 31 Dec 2016 11:28:59 GMT