Carrying forward row value to calculate next row (by Date,SKU)
I have following type of data for which i need a qlik sense script to be written or if this can be achieved in a visual or both, using set expressions etc.
RowNumber
SKU Code
Physical Qty
Expected Qty
Forecast Qty
Balance
1
ABD
20-07-2024
100
300
100
300
1
PPF
20-07-2024
200
200
100
300
1
DDF
20-07-2024
300
700
200
800
1
KKR
20-07-2024
400
900
200
1100
1
RCB
20-07-2024
250
450
100
600
1
PPU
20-07-2024
150
350
100
400
2
ABD
21-07-2024
100
300
100
600
2
PPF
21-07-2024
200
200
100
600
2
DDF
21-07-2024
300
700
200
1600
2
KKR
21-07-2024
400
900
200
2200
2
RCB
21-07-2024
250
450
100
1200
2
PPU
21-07-2024
150
350
100
800
3
ABD
22-07-2024
200
100
50
850
3
PPF
22-07-2024
200
100
50
850
3
DDF
22-07-2024
200
100
50
1850
3
KKR
22-07-2024
200
100
50
2450
3
RCB
22-07-2024
200
100
50
1450
3
PPU
22-07-2024
200
100
50
1050
For Day 1 (First Date) for a particular SKU: Physical Qty + Expected Qty - Forecast = Balance For Day 2 and Onwards, for that particular SKU: Physical Qty + Expected Qty + (Previous Date's Balance) - Forecast = Balance
This means the balance quantity of the previous date for that SKU is carried over to the next date to calculate that day's balance.
The Qlik script i wrote gives me correct output till day 2/ That is because the balance of Day 1 is not dependent on previous day's balance. The calculations fail thereafter.
