Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
maybe
if(Region=23, RangeSum(above( sum(qty),1,rowno()-1)), sum(qty))
use this....
if(Region=23,RangeSum(above(TOTAL SUM(Data),1,RowNo())),Data)
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.
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?
used but same problem
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..?
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 ....
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 ....
as already replied... you can't use Column(1) here.. use SUM(Data) instead..