Reference a previous row in table.

[#1]

Hi Qlik

I am looking at a problem where I have a site that produces 10 units a day and has capacity of 50units. I want to put together a delivery schedule where I'm cumulating the number of units on site that resets once the site reaches 50 eg see table below:

Day	Quantity
Monday	10
Tuesday	20
Wednesday	30
Thursday	40
Friday	50
Saturday	10
Sunday	20

The formula needs to be something along the line of

IF( Previous days quantity (Q-1) + Increase (P) > Capacity (C),

Q-1 + P -C,

Q-1 +P)

However I don't know how to reference the previous quantity. When culminating values before I have used rangesum(above( but to be entirely honest don't full understand the function.

Any help would be much appreciated.

Kind regards

[Gysbert_Wassenaar · Accepted solution]

Something like this maybe:

LOAD

     Day,

     Quantity,

     If(Quantity+Peek(CumulativeQuantity)> Capacity, Quantity, Quantity+Peek(CumulativeQuantity)) as CumulativeQuantity

FROM

     ....

[#3]

Hi Gysbert,
Thanks for getting back so quickly.

The capacity and quantity are being drawn from two separate data loads, will this be an issue?

[sunny_talwar]

Some useful links to look at for script options

Peek() vs Previous() – When to Use Each

Peek() or Previous() ?‌

For front end chart objects, you can check this link out

Missing Manual - Above() and Below()

[vinieme12]

can you post a sample of your desired output

[Gysbert_Wassenaar]

Not if you first join them into one table. Otherwise yes. A load can only reference fields from its only source table.

[#7]

Thanks Gysbert