Cumulative Balance Calculation

[Manguirish]

I have following type of data (attached) 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.

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.

[Manguirish]

I have following type of data (attached) 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.

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.

[morgankejerhag]

I would solve it like this:

DataIn:

LOAD * INLINE [

    RowNumber, SKU Code, Date, Physical Qty, Expected Qty, Forecast Qty, ExcelBalance

    1, ABD, 2024-07-20, 100, 300, 100, 300

    1, PPF, 2024-07-20, 200, 200, 100, 300

    1, DDF, 2024-07-20, 300, 700, 200, 800

    1, KKR, 2024-07-20, 400, 900, 200, 1100

    1, RCB, 2024-07-20, 250, 450, 100, 600

    1, PPU, 2024-07-20, 150, 350, 100, 400

    2, ABD, 2024-07-21, 100, 300, 100, 600

    2, PPF, 2024-07-21, 200, 200, 100, 600

    2, DDF, 2024-07-21, 300, 700, 200, 1600

    2, KKR, 2024-07-21, 400, 900, 200, 2200

    2, RCB, 2024-07-21, 250, 450, 100, 1200

    2, PPU, 2024-07-21, 150, 350, 100, 800

    3, ABD, 2024-07-22, 200, 100, 50, 850

    3, PPF, 2024-07-22, 200, 100, 50, 850

    3, DDF, 2024-07-22, 200, 100, 50, 1850

    3, KKR, 2024-07-22, 200, 100, 50, 2450

    3, RCB, 2024-07-22, 200, 100, 50, 1450

    3, PPU, 2024-07-22, 200, 100, 50, 1050

];

Data:

Load

*,

    if(peek([SKU Code])<>[SKU Code],

    [Physical Qty]+[Expected Qty]-[Forecast Qty],

        [Physical Qty]+[Expected Qty]-[Forecast Qty]+peek(Balance)

) as Balance

resident DataIn order by [SKU Code], Date asc;

drop table DataIn;