QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor II

Calculate Field in Script that is a Sum of Current Row and Previous Two

I'm having trouble finding the answer to this in the forum. Previous and Peek with RangeSum come close but neither get the result I need.

I need to create a new field in the script that takes the value of the current row, plus the previous two. The data needs to be grouped by plant and fiscal period. Example data is below...

 Plant FiscalPeriod Line Item Value Value2 (sum of current and previous 2 rows) MA Aug/18 Expense 416,772 MA Sep/18 Expense 564,844 MA Oct/19 Expense 90,698 1,072,314 MA Nov/19 Expense 11,035 666,577 MA Dec/19 Expense 27,932 129,665 MA Jan/19 Expense 1,320 40,287 MA Feb/19 Expense 9,332 38,584 MA Mar/19 Expense 17,537 28,189 MA Apr/19 Expense 87,579 114,448 MA May/19 Expense 69,482 174,598
Labels (5)

• Script

1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

Re: Calculate Field in Script that is a Sum of Current Row and Previous Two

try like this:

FiscalPeriod,
[Line Item],
Value,
Value + peek(Value,-1)+peek(Value,-2) as cumValue
FROM
[https://community.qlik.com/t5/QlikView-Scripting/Calculate-Field-in-Script-that-is-a-Sum-of-Current-...]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Col, Pos(Top, 5))));

4 Replies
Highlighted
Honored Contributor II

Re: Calculate Field in Script that is a Sum of Current Row and Previous Two

try like this:

FiscalPeriod,
[Line Item],
Value,
Value + peek(Value,-1)+peek(Value,-2) as cumValue
FROM
[https://community.qlik.com/t5/QlikView-Scripting/Calculate-Field-in-Script-that-is-a-Sum-of-Current-...]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Col, Pos(Top, 5))));

Highlighted
New Contributor II

Re: Calculate Field in Script that is a Sum of Current Row and Previous Two

Hi Frank,

Thank you for the response. I tried that one and it doesn't work as expected. It creates three records for each value and takes the sum of those three values rather than the sum of the current and prior months. I'm assuming it has something to do with the grouping of the data.

 Plant FiscalPeriod Value cumValue MA Nov/19 11035.41 67209.99 MA Nov/19 11035.41 100158.11 MA Nov/19 11035.41 33106.23
Highlighted
Honored Contributor II

Re: Calculate Field in Script that is a Sum of Current Row and Previous Two

can you share the qvw or at least a sample qvw which shows the issue?

Highlighted
New Contributor II

Re: Calculate Field in Script that is a Sum of Current Row and Previous Two

The solution worked. I had something in my script that was duplicating the rows. Thank you!