2

I have a table like this :

Date Amount
06/Jan/2021 300
05/Jan/2021 200
04/Jan/2021 -100
03/Jan/2021 -500
02/Jan/2021 200
01/Jan/2021 100

I want to get a column with the running totals but keeping the min amount to 0. The total resets to 0 once its below 0.

the expected outcome is as below

Date Amount Total
06/Jan/2021 300 500
05/Jan/2021 200 200
04/Jan/2021 -100 0
03/Jan/2021 -500 0
02/Jan/2021 200 200
01/Jan/2021 100 100
JonJon
  • 41
  • 4

1 Answers1

1

First we need to sort our data.

Second we need to select previous total and add current amount + add condition.

Third we need sort our table way as we like (newest on top).

Most important function used is Peek.

// example data (I simplified data)
[data]:
LOAD 
    * 
INLINE [
    Date, Amount
    6, 300
    5, 200
    4, -100
    3, -500
    2, 200
    1, 100
];

// order as we need use data from previous row correctly
[data_ordered]:
NoConcatenate LOAD 
    *
Resident
    [data]
Order by 
    Date asc;
    
DROP Table [data];

// condition, peek function and alt needed for first row where is no data yet
[data_totals]:
LOAD
    Date,
    Amount,
    If((Amount + Alt(Peek('Total', -1), 0)) > 0,
        Amount + Alt(Peek('Total', -1), 0),
        0) as Total
Resident
    [data_ordered];
    
DROP Table [data_ordered];

final table:

table created with Peek qlikview function

Hubert Dudek
  • 1,666
  • 1
  • 13
  • 21
  • Hi if it is ok please give a vote and select as correct answer. If you have new problem please send me link to new question. I am away on vacation but will back on Monday and will look to it. Bests Hubert – Hubert Dudek May 26 '21 at 09:21
  • Thanks Hubert, this one worked for me. But i have a different problem now. i have listed below – JonJon May 26 '21 at 09:32