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

[Anonymous]

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,

[Anonymous]

Something like this?

Load *,

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

Resident MyTable;

[Anonymous]

Above function is not working in script,

Thanks

[Anonymous]

Is there a way to perform this function in script?

[Anonymous]

Can you provide Sample QVW app with sample data?

[tresesco · Accepted solution]

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.

[Anonymous]

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.

[jyothish8807]

Hi ,

check the attached.

Br,

KC

[Anonymous]

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.

[jyothish8807]

A:

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

B:

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;