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.
Please help.
Thanks
Manguirish
