Calculate the Forecast QOH based on Current Month Inventory QOH(Quantity On Hand)/future Demand Units/ future Production Units

[vinayak_rastogi_terex]

Hello Everyone, 

I really looking forward for your help and support, in Qlik Script side

Future QOH Formula for Calculation - (Current Month Inventory QOH (Jan-2024)  + Production Units (Feb-2024) ) - Demand Units (Feb-2024)

Likewise same for other months future months,  Based calculated Future QOH for next Month March 2024

I've try that in Qlik Script -

T2:

Load

    %Key,

    Date(CalMonth, 'MMM-YYYY') as CalMonthDate,

    Prod,

    Geo,

    model,

    Mfg_location,

    SUM(quantity_on_hand) as SUMQty,

    SUM(Demand_Units) as Demand_UT,

    SUM(Production_Units) as Production_UT

Resident Table1 

Group by %Key, Geo, Prod, model, Mfg_location, CalMonth

Order By model, Geo, Prod, Mfg_location, CalMonth asc;

NoConcatenate

FutureQOHTemp:

Load

    RowNo() as ID,

    %Key,

    CalMonthDate,

    Prod,

    Geo,

    model,

    Mfg_location,

    SUMQty,

    Demand_UT,

    Production_UT,

     IF(model <> Previous(model) ,SUMQty,(Peek(StockNew)+Production_UT)-Demand_UT)  as StockNew

Resident T2

Order By Geo, Prod, model, Mfg_location, CalMonthDate asc;

Output based on above script -  Red Circle Output showcasing wrongly, other side showcasing the output for "King County" is correct (Highlighted in Yellow) 

Thank you!