Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ananyaghosh
Contributor III

Need help for above() function

Hi,

I have the below data

   

11
23
36
410
515
621
728
836
945

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

Tags (1)
13 Replies
Frank_Hartmann
Honored Contributor II

Re: Need help for above() function

try this


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


Chanty4u
Esteemed Contributor III

Re: Need help for above() function

try this

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

ananyaghosh
Contributor III

Re: Need help for above() function

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.

Re: Need help for above() function

May be look into using The As-Of Table

ananyaghosh
Contributor III

Re: Need help for above() function

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

Re: Need help for above() function

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
Contributor III

Re: Need help for above() function

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

Re: Need help for above() function

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
Contributor III

Re: Need help for above() function

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()))
   
PT
217430.00217430
24859.97242289.97
0242289.97
3014.883014.88
03014.88
03014.88
4003414.88
2127828.452131243.33
02131243.33
02131243.33

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

Thanks,

Sandip