Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
vigneshea
Contributor III
Contributor III

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

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

Capture1.JPG

and I need answers in this way,

Capture2.JPG

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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.

View solution in original post

11 Replies
daniel_rodrigue
Contributor III
Contributor III

Something like this?

Load *,

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

Resident MyTable;

vigneshea
Contributor III
Contributor III
Author

Above function is not working in script,

Thanks

vigneshea
Contributor III
Contributor III
Author

Is there a way to perform this function in script?

ashokkumarm
Contributor
Contributor

Can you provide Sample QVW app with sample data?

tresesco
MVP
MVP

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.

vigneshea
Contributor III
Contributor III
Author

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
Master II
Master II

Hi ,

check the attached.

Br,

KC

Best Regards,
KC
vigneshea
Contributor III
Contributor III
Author

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
Master II
Master II

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;

Best Regards,
KC