topic Re: Need help for above() function in QlikView

Need help for above() function

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help for above() function

Frank_Hartmann — Tue, 11 Jul 2017 10:24:06 GMT

try this

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

Re: Need help for above() function

Chanty4u — Tue, 11 Jul 2017 10:24:09 GMT

try this

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

Re: Need help for above() function

ananyaghosh — Tue, 11 Jul 2017 10:52:59 GMT

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

sunny_talwar — Tue, 11 Jul 2017 11:22:03 GMT

May be look into using The As-Of Table

Re: Need help for above() function

ananyaghosh — Tue, 11 Jul 2017 11:56:03 GMT

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

Re: Need help for above() function

sunny_talwar — Tue, 11 Jul 2017 11:59:33 GMT

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

Re: Need help for above() function

ananyaghosh — Tue, 11 Jul 2017 12:20:07 GMT

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

sunny_talwar — Tue, 11 Jul 2017 12:26:13 GMT

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?

Re: Need help for above() function

ananyaghosh — Wed, 12 Jul 2017 08:02:19 GMT

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

Re: Need help for above() function

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:16:47 GMT

May be this?

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

Re: Need help for above() function

ananyaghosh — Wed, 12 Jul 2017 08:31:50 GMT

Hi,

Your solution does not work for me and it behave like same as before.

Thanks,

Sandip

Re: Need help for above() function

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:40:33 GMT

May be change it to Above rather Below ??

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

For me, it's working with out change the script?

Pivot table?

Re: Need help for above() function

ananyaghosh — Wed, 12 Jul 2017 09:52:52 GMT

hi,

I got the below data set at column CUM after I have used your set analysis:

YTD	CUM
217430	217,430.00
24859.97	242,289.97
0	242,289.97
3014.88	3,014.88
0	3,014.88
0	3,014.88
400	3,414.88
2127828.45	2,131,243.33
0	2,131,243.33
0	2,131,243.33
7048.61	2,138,291.94
5150	2,143,441.94
1675367.7	1,675,367.70

and you can see that for last row and 4th row it is not working. So any suggestion for this?

Thanks,

Sandip