topic Re: How to calculate Rangesum for above 12 rows in Script? in QlikView

How to calculate Rangesum for above 12 rows only in Script?

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

Hi,

I need to calculate the RangeSum of Current month with above 12 month,

like this I need to calculate it for every month,

I'm having a table like this

and I need answers in this way,

Re: How to calculate Rangesum for above 12 rows in Script?

Anonymous — Fri, 08 Jun 2018 02:34:10 GMT

Something like this?

Load *,

RangeSum (Above(Value,0,12)) As result

Resident MyTable;

Re: How to calculate Rangesum for above 12 rows in Script?

Anonymous — Fri, 08 Jun 2018 04:07:11 GMT

Above function is not working in script,

Thanks

Re: How to calculate Rangesum for above 12 rows in Script?

Anonymous — Fri, 08 Jun 2018 05:55:11 GMT

Is there a way to perform this function in script?

Re: How to calculate Rangesum for above 12 rows only in Script?

Anonymous — Fri, 08 Jun 2018 07:01:22 GMT

Can you provide Sample QVW app with sample data?

Re: How to calculate Rangesum for above 12 rows only in Script?

tresB — Fri, 08 Jun 2018 07:18:43 GMT

As an alternative, you could try using multiple peek()s like:

Load

RangeSum(Peek(Field), Peek(Field, -2), Peek(Field, -3)....Peek(Field, -12)) as Result

Here you have to be careful about the load order.

Re: How to calculate Rangesum for above 12 rows only in Script?

Anonymous — Fri, 08 Jun 2018 07:23:09 GMT

Here the answer is generated with rangesum and above function in Chart expressions ,

But I am in need of to get this done in Script.

Re: How to calculate Rangesum for above 12 rows only in Script?

jyothish8807 — Fri, 08 Jun 2018 07:37:57 GMT

Hi ,

check the attached.

Br,

Re: How to calculate Rangesum for above 12 rows only in Script?

Anonymous — Fri, 08 Jun 2018 07:38:19 GMT

Thanks Mate, Your Code nearly worked and I've got an Idea about the Problem.

I've made some changes to the code you provided, nothing but I've added Field name "Value" in addition.

//RangeSum(Value,Peek(Value,-1),Peek(Value,-2),..........Peek(Value,-10) ,Peek(Value,-11) ) as Result.

Above code works perfectly.

Re: How to calculate Rangesum for above 12 rows only in Script?

jyothish8807 — Fri, 08 Jun 2018 07:39:05 GMT

LOAD * INLINE [

Month, sale

Jan-15, 71

Feb-15, 99

Mar-15, 76

Apr-15, 88

May-15, 91

Jun-15, 28

Jul-15, 25

Aug-15, 37

Sep-15, 58

Oct-15, 40

Nov-15, 93

Dec-15, 40

Jan-16, 86

Feb-16, 18

Mar-16, 34

Apr-16, 15

May-16, 35

Jun-16, 17

Jul-16, 9

Aug-16, 11

Sep-16, 20

Oct-16, 3

Nov-16, 51

Dec-16, 94

];

NoConcatenate

load Date#(Month,'MMM-YY') as Date,

sale,

rangesum(sale,peek(sale,-1),peek(sale,-2),peek(sale,-3),peek(sale,-4),peek(sale,-5),peek(sale,-6),peek(sale,-7),peek(sale,-8),

peek(sale,-9),peek(sale,-10),peek(sale,-11),peek(sale,-12)) as SaleNew

resident A;

drop table A;

Re: How to calculate Rangesum for above 12 rows only in Script?

Anonymous — Fri, 08 Jun 2018 07:46:40 GMT

Thanks Mate ,

I've deleted this extra field in the code (Peek(Value,-12))

and It works fine.

Thanks for your Time.

Re: How to calculate Rangesum for above 12 rows only in Script?

jyothish8807 — Fri, 08 Jun 2018 08:12:21 GMT

You are welcome. you can close the thread if you have got the answer