Simple calculate open and closing balances

[#1]

Hi

Could someone explain the simplest method of calculating open and closing balances by Month for a product if we have the product # ,description and current stock in one table and a series of supply and demand records by due date in another table?

eg  ProductA

     Current-Stock    200

     March Demand   50

     March Supply     30        Need to calculate March closing Stock = 200 - 50 + 30 =  180

     April Demand    100

     April Supply        60       Need to calculate April Closing Stock = 180 - 100  + 60 = 140

     etc

I can produce a simple Pivot table with dimension Part and Month and expressions for the Demand and Supply columns but I don't know how to get the relative closing balance?

Many Tks

Dave

[CELAMBARASAN]

Hi,

     Check with this,

     Temp:

     Load

          Part#,

          MonthName(DueDate) as Months,

          Supply,

          Demand

      Resident Supply/Demand Order by DueDate Asc;

     Left Join(Temp)

     Load

          Part#,

          Qty-On-Hand as ClosingBalance

     Resident Inventory;

     Output:

     Load

          Part#,

          Months,

          if(Previous(ClosingBalance)<> null(),Previous(ClosingBalance),ClosingBalance)+Supply-Demand as ClosingBalance

     Resident Temp:

     Drop Table Temp;

     Hope it helps

Celambarasan

[#3]

Hi Celambarasan

Tried both methods – still have same problem – End-Bal only showing figure for relevant month

Regards

Dave

[#4]

Tks Celambarasan

I will try later tonight – I have to go off site for a meeting now

Your help is much appreciated

Regards

Dave

[#5]

Tks Celambarasan – tried this but having difficulty with syntax – sorry I’m still not up to speed with Qlikview

[preminqlik]

send your sample data of all the required tables.

so that we can try in script..

[Anonymous]

Hi David,

This is something that I created some time ago.  Maybe it's useful.

Regards,

Filip