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

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

egoss_cynosure_ — Sat, 16 Nov 2024 02:49:27 GMT

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!

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

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

Frank_Hartmann — Sat, 03 Aug 2019 18:56:40 GMT

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-Row-and/td-p/1609116]
(html, codepage is 1252, embedded labels, table is @1, filters(Remove(Col, Pos(Top, 5))));

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

egoss_cynosure_ — Sat, 03 Aug 2019 19:08:44 GMT

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

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

Frank_Hartmann — Sun, 04 Aug 2019 09:05:53 GMT

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

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

egoss_cynosure_ — Thu, 08 Aug 2019 16:24:29 GMT

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