Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have data like this.
Item | Incr | Decr |
A | 1 | 0 |
A | 2 | 1 |
A | 3 | 1 |
B | 4 | 0 |
B | 2 | 1 |
B | 5 | 3 |
I want to a running total for each item and the output should look like this
Item | Incr | Decr | RunningTotal |
A | 1 | 0 | 1 |
A | 2 | 1 | (1+2) - 1 = 2 |
A | 3 | 1 | (2+3) - 1 = 4 |
B | 4 | 0 | 4 |
B | 2 | 1 | (4+2)-1 = 5 |
B | 5 | 3 | (5+5) - 3 = 7 |
ItemRunningTotal:
Load Item, incr, decr
if (peek(Item) = Item, rangesum(peek(RunningTotal), Incr, -Decr)) as RunningTotal
Resident Item;
Is it correct?
Thanks
Try like this:
tmp:
LOAD Item,
Incr,
Decr
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/if-peek-and-rangesum-in-script/td-p/1781908]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
NoConcatenate
Final:
LOAD *,
If(Item=Previous(Item), RangeSum(Peek('RUNNING_TOTAL'), Incr-Decr), Incr-Decr) as RUNNING_TOTAL
Resident tmp Order By Item, Decr; DROP table tmp;
Try like this:
tmp:
LOAD Item,
Incr,
Decr
FROM
[https://community.qlik.com/t5/QlikView-App-Dev/if-peek-and-rangesum-in-script/td-p/1781908]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);
NoConcatenate
Final:
LOAD *,
If(Item=Previous(Item), RangeSum(Peek('RUNNING_TOTAL'), Incr-Decr), Incr-Decr) as RUNNING_TOTAL
Resident tmp Order By Item, Decr; DROP table tmp;