Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))));
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))));
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 |
can you share the qvw or at least a sample qvw which shows the issue?
The solution worked. I had something in my script that was duplicating the rows. Thank you!