Qlik Community

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
egoss_cynosure_
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...

Any suggestion? Thanks in advance!

PlantFiscalPeriodLine ItemValueValue2 (sum of current and previous 2 rows)
MAAug/18Expense                       416,772 
MASep/18Expense                       564,844 
MAOct/19Expense                         90,698                                                                           1,072,314
MANov/19Expense                         11,035                                                                               666,577
MADec/19Expense                         27,932                                                                               129,665
MAJan/19Expense                           1,320                                                                                 40,287
MAFeb/19Expense                           9,332                                                                                 38,584
MAMar/19Expense                         17,537                                                                                 28,189
MAApr/19Expense                         87,579                                                                               114,448
MAMay/19Expense                         69,482                                                                               174,598
1 Solution

Accepted Solutions
Highlighted
Frank_Hartmann
Honored Contributor II

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

try like this:

 

LOAD Plant,
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))));

View solution in original post

4 Replies
Highlighted
Frank_Hartmann
Honored Contributor II

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

try like this:

 

LOAD Plant,
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))));

View solution in original post

Highlighted
egoss_cynosure_
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.

PlantFiscalPeriodValuecumValue
MANov/1911035.4167209.99
MANov/1911035.41100158.11
MANov/1911035.4133106.23
Highlighted
Frank_Hartmann
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
egoss_cynosure_
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!