Cumulative Balance Calculation

Manguirish — Tue, 16 Jul 2024 07:09:40 GMT

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.

Re: Cumulative Balance Calculation

morgankejerhag — Wed, 12 Feb 2025 21:22:45 GMT

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;

topic Re: Cumulative Balance Calculation in App Development

Cumulative Balance Calculation

Re: Cumulative Balance Calculation