Need help for above() function

[ananyaghosh]

Hi,

I have the below data

1	1
2	3
3	6
4	10
5	15
6	21
7	28
8	36
9	45

You can see that for 2nd column, we need to add cumulative sum of values based on first column and I have used the below set expression:-

rangesum(Above(sum([Sales Amt]))) + column(1)

but it does not work for me.

Thanks,

Sandip Ghosh

[Frank_Hartmann]

try this

rangesum(Above(sum([Sales Amt]),0,RowNo()))

[Chanty4u]

try this

RangeSum(Above(Count(ID), 0, RowNo()))

[ananyaghosh]

Hi,

One more problem is seems to be appeared is when I use condition in set analysis it does not work properly:

rangesum(above(sum({$<[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=Max([Process Date]))"}>}[Sales Amt]),0,RowNo()))

and it is happening only when I got 0 for one row and after that row value is not calculating correctly.

So please help me for this.

[sunny_talwar]

May be look into using The As-Of Table

[ananyaghosh]

can u check my set analysis and tell me what is wrong in it?

[sunny_talwar]

Doesn't look wrong to me... do you have a sample to share where it isn't doing what you want it to do

[ananyaghosh]

Hi,

I have attached my sample data, please look into this and it seems that sometimes it just adding the previous row value and sometimes not. So I am confused with this type of behavior.

Thanks,

Sandip

[sunny_talwar]

A and B are the columns? Where is Process Date?

Also, can you load your data in a qvw file for me? Is it that difficult to do that and create the objects so that it makes my life easier in helping you resolve your issue?

[ananyaghosh]

Hi,

I cannot create a QVW with some sample data for security reason as because my company has very strict security rules over the data. But I have find out the root cause of the problem by analyzing the data and if you look at the below data set, you can see that whenever 0 appears in the data set of column P it got incorrect result in Column T.

For P column I have used the below set expression:

sum({$<[Proc Date]={">=$(=YearStart(Max([Proc Date]))) <=$(=Max([Proc Date]))"}>}[Amount])

and for T column I have used the below set expression:

rangesum(above(sum({$<[Proc Date]={">=$(=YearStart(Max([Proc Date]))) <=$(=Max([Proc Date]))"}>}[Amount]),0,RowNo()))

   

P	T
217430.00	217430
24859.97	242289.97
0	242289.97
3014.88	3014.88
0	3014.88
0	3014.88
400	3414.88
2127828.45	2131243.33
0	2131243.33
0	2131243.33

So is there any work around to over come this tricky problem?

Thanks,

Sandip